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

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,389 views