Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to create a grand total

User_6Q8J8Dec 30 2011 — edited Jan 3 2012
My query works fine. I am just wondering if I could add another row to create a grand total for all rows returned running this query. please help
SELECT DISTINCT 'Q'||(TRUNC (TO_CHAR (B.DATELET, 'Q')))||' - ' || :year QuarterYear ,TRUNC (TO_CHAR (B.DATELET, 'Q')) Quarter,
 to_char (sum(l.contamt) OVER (PARTITION BY  (TRUNC (TO_CHAR (B.DATELET, 'Q')))) ,   '$999,999,999.99') AwardedDollarAmount ,

       count(P.CPROJNUM) OVER (PARTITION BY  (TRUNC (TO_CHAR (B.DATELET, 'Q')))) ProjectAwarded,
     ---  to_char( SUM (MIN (c.calcbtot)) OVER (PARTITION BY  (TRUNC (TO_CHAR (B.DATELET, 'Q')))), '$999,999,999.99') AwardedDollarAmount,
       SUM (COUNT (C.VENDOR))OVER (PARTITION BY  (TRUNC (TO_CHAR (B.DATELET, 'Q'))))  NumberOfBidders,
       to_char(SUM (COUNT (C.VENDOR))OVER (PARTITION BY  (TRUNC (TO_CHAR (B.DATELET, 'Q')))) /  count(P.CPROJNUM) OVER (PARTITION BY  (TRUNC (TO_CHAR (B.DATELET, 'Q')))), '999.99') AverageNumberOfBidder
    FROM BIDDERS C,
         BIDLET B,
         LETPROP L,
         PROPOSAL P
   WHERE     C.LETTING = B.LETTING
         AND P.CONTID = L.LCONTID
         AND C.LETTING = L.LETTING
         AND C.CALL = L.CALL
         AND l.lcontid IN
                (SELECT lcontid
                 FROM letprop c, PROPOSAL d
                 WHERE     datestat IS NOT NULL
                        AND UPPER (letstat) <> 'R'
                        AND UPPER (letstat) <> 'B'
                        AND TRIM (UPPER (TIMELET)) = TRIM ('9:30 A.M.')
                        AND c.LCONTID = d.CONTID)
         AND B.DATELET >=
                ADD_MONTHS (TRUNC (TO_DATE (:year, 'YYYY'), 'YYYY'),
                            (:from_quarter - 1) * 3)
         AND B.DATELET <
                ADD_MONTHS (TRUNC (TO_DATE (:year, 'YYYY'), 'YYYY'),
                            :to_quarter * 3)
GROUP BY  TRUNC (TO_CHAR (B.DATELET, 'Q')), P.CPROJNUM, l.contamt
order by  TRUNC (TO_CHAR (B.DATELET, 'Q'));



Q1 - 2011	1	 $174,950,864.40	71	278	   3.92
Q2 - 2011	2	 $196,470,118.71	96	329	   3.43
Q3 - 2011	3	  $56,552,583.92	30	116	   3.87
Q4 - 2011	4	  $25,497,261.10	17	84	   4.94
Total  Amount:            35000047800.00       214           807******************************Here is what I would like to create a new row to show grand total.
Edited by: 893601 on Dec 30, 2011 12:07 PM
This post has been answered by Frank Kulash on Dec 30 2011
Jump to Answer

Comments

Karthick2003
What do you mean by compare? Can you explain a bit more?
Dbb
Select a.empcode, sum(a.totalvalue) , sum(b.totalvalue )
from table_sales a, table_sales b
where a.empcode = b.empcode
and a.trndate between ......
and b.trndate between ........
group by a.empcode

Edited by: Agustin UN on 08-nov-2010 13:22
726742
Do you mean Sum(totalvalue) between those periods? 
select 
Select sum(totalvalue) from table where trndate between to_date('01-10-2010', 'DD-MM-YYYY') AND  to_date('08-01-2010', 'DD-MM-YYYY')

SELECT ((SELECT SUM(totalvalue)
         FROM   table1
         WHERE  trndate BETWEEN to_date('01-10-2010', 'DD-MM-YYYY') AND
                to_date('08-10-2010', 'DD-MM-YYYY')) -
       (SELECT SUM(totalvalue)
         FROM   table1       
         WHERE  trndate BETWEEN to_date('01-11-2010', 'DD-MM-YYYY') AND
                to_date('01-11-2010', 'DD-MM-YYYY')))
FROM   dual
Be more clear on your requirement :)
787094
I need some what like that, but there is a problem that if there is no sales in one range reports are not coming properly.

Please help

Thanks

Niloy
726742 
SELECT ((SELECT SUM(nvl(totalvalue,0))
         FROM   table1
         WHERE  trndate BETWEEN to_date('01-10-2010', 'DD-MM-YYYY') AND
                to_date('08-10-2010', 'DD-MM-YYYY')) -
       (SELECT SUM(nvl(totalvalue,0))
         FROM   table1       
         WHERE  trndate BETWEEN to_date('01-11-2010', 'DD-MM-YYYY') AND
                to_date('01-11-2010', 'DD-MM-YYYY')))
FROM   dual
787094
I have table with the following data :

custcode emp Trndate Totalvalue
1 1 2010-10-01 500.00
2 1 2010-10-02 500.00
3 1 2010-10-03 500.00
4 1 2010-10-08 100.00
5 1 2010-11-02 175.00
6 5 2010-11-01 130.00
7 5 2010-11-02 135.00
8 5 2010-11-03 130.00
9 6 2010-10-01 100.00
10 6 2010-10-02 12.00


I'd like to build a query that returns something like this:

Empcode TotalSales_OCT TotalSales_NOV
====== ========= ===========
1 1600 175
5 0 395
6 112 0


Please help...

Thanks
Dbb
Select a.empcode,b.empcode, sum(a.totalvalue) , sum(b.totalvalue )
from table_sales a, table_sales b
where a.empcode(+) = b.empcode
and a.trndate(+) between ......
and b.trndate(+) between ........
group by a.empcode
Union
Select a.empcode,b.empcode, sum(a.totalvalue) , sum(b.totalvalue )
from table_sales a, table_sales b
where a.empcode = b.empcode(+)
and a.trndate(+) between ......
and b.trndate(+) between ........
group by a.empcode

Edited by: Agustin UN on 08-nov-2010 13:50
Dbb
with table_sales as (
select 1 custcode, 1 empcode, '2010-10-01' Trndate, 500.00 Totalvalue from dual union
select 2, 1, '2010-10-02', 500.00 from dual union
select 3, 1, '2010-10-03', 500.00 from dual union
select 4, 1, '2010-10-08', 100.00 from dual union
select 5, 1, '2010-11-02', 175.00 from dual union
select 6, 5, '2010-11-01', 130.00 from dual union
select 7 ,5, '2010-11-02', 135.00 from dual union
select 8, 5, '2010-11-03', 130.00 from dual union
select 9, 6, '2010-10-01', 100.00 from dual union
select 10, 6, '2010-10-02', 12.00 from dual
)
Select a.empcode, sum(a.totalvalue) , sum(b.totalvalue)
from table_sales a ,table_sales b
where
a.empcode = b.empcode(+)
and a.Trndate(+) between '2010-10-01' and '2010-10-31'
and b.Trndate(+) between '2010-11-01' and '2010-11-30'
group by a.empcode
union
Select b.empcode empcode, sum(a.totalvalue) , sum(b.totalvalue)
from table_sales a ,table_sales b
where
a.empcode(+) = b.empcode
and a.Trndate(+) between '2010-10-01' and '2010-10-31'
and b.Trndate(+) between '2010-11-01' and '2010-11-30'
group by b.empcode
Aketi Jyuuzou
In this case,Pivot clause is not useful :D
with table_sales(custcode,empcode,Trndate,Totalvalue) as(
select 1, 1,date '2010-10-01',500.00 from dual union
select 2, 1,date '2010-10-02',500.00 from dual union
select 3, 1,date '2010-10-03',500.00 from dual union
select 4, 1,date '2010-10-08',100.00 from dual union
select 5, 1,date '2010-11-02',175.00 from dual union
select 6, 5,date '2010-11-01',130.00 from dual union
select 7 ,5,date '2010-11-02',135.00 from dual union
select 8, 5,date '2010-11-03',130.00 from dual union
select 9, 6,date '2010-10-01',100.00 from dual union
select 10,6,date '2010-10-02', 12.00 from dual)
select empcode,
sum(case when Trndate
         between date '2010-10-01' and date '2010-10-08'
         then Totalvalue else 0 end) as OCT,
sum(case when Trndate
         between date '2010-11-01' and date '2010-11-08'
         then Totalvalue else 0 end) as NOV
  from table_sales
group by empcode
order by empcode;

EMPCODE   OCT  NOV
-------  ----  ---
      1  1600  175
      5     0  395
      6   112    0
ramanlakunde
SELECT ((SELECT SUM(nvl(totalvalue,0))
FROM table1
WHERE trndate BETWEEN to_date('01-10-2010', 'DD-MM-YYYY') AND
to_date('08-10-2010', 'DD-MM-YYYY') group by empcode) -
(SELECT SUM(nvl(totalvalue,0))
FROM table1
WHERE trndate BETWEEN to_date('01-11-2010', 'DD-MM-YYYY') AND
to_date('01-11-2010', 'DD-MM-YYYY') group by empcode))
FROM dual
787094
Hi,
If I put this query I got the following error :

SELECT ((SELECT SUM(nvl(dispvalue,0))
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row


Thanks
Niloy
1 - 11
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 31 2012
Added on Dec 30 2011
4 comments
49,312 views