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