Getting Grand Totals using Group By

sql, sql-server-2008

Solution

You are looking for the ROLLUP operator which would add a grand total row at the end of the result set. If you are looking for more complex aggregate totals use ROLLUP or CUBE with the GROUP BY clause, such as the link provided by @MartinSmith or Aggregation WITH ROLLUP

SELECT 
    SUM(dbo.tbl_orderitems.mon_orditems_pprice) AS prodTotal,
    AVG(dbo.tbl_orderitems.mon_orditems_pprice) AS avgPrice,
    count(dbo.tbl_orderitems.uid_orditems_prodid) AS prodQty,
    dbo.tbl_orderitems.txt_orditems_pname
FROM 
    dbo.tbl_orderitems
INNER JOIN 
    dbo.tbl_orders ON (dbo.tbl_orderitems.uid_orditems_orderid = dbo.tbl_orders.uid_orders)
WHERE 
    dbo.tbl_orders.uid_order_webid = <cfqueryparam cfsqltype="cf_sql_integer" value="#session.webid#">
    AND dbo.tbl_orders.txt_order_status = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.sale_status#">
GROUP BY
    dbo.tbl_orderitems.txt_orditems_pname
WITH ROLLUP

Problem

I am trying to teach myself more about SQL at the moment and am currently trying to carry out some simple sales reporting using `SUM`, `COUNT`, `AVG` and `GROUP BY` functions within a SQL Server 2008 database. I have managed to get the total, count and average of each group by row. How do I get the grand total of all the group by rows? SQL so far: ``` SELECT SUM(dbo.tbl_orderitems.mon_orditems_pprice) AS prodTotal, AVG(dbo.tbl_orderitems.mon_orditems_pprice) AS avgPrice, count(dbo.tbl_orderitems.uid_orditems_prodid) AS prodQty, dbo.tbl_orderitems.txt_orditems_pname FROM dbo.tbl_orderitems INNER JOIN dbo.tbl_orders ON (dbo.tbl_orderitems.uid_orditems_orderid = dbo.tbl_orders.uid_orders) WHERE dbo.tbl_orders.uid_order_webid = <cfqueryparam cfsqltype="cf_sql_integer" value="#session.webid#"> AND dbo.tbl_orders.txt_order_status = <cfqueryparam cfsqltype="cf_sql_varchar" value="#arguments.sale_status#"> GROUP BY dbo.tbl_orderitems.txt_orditems_pname ``` ``` Product Qty Gross Avg Westbury Climbing Frame 17 8,023.00 471.94 Sandpoint Deluxe Climbing Frame 34 36,146.00 1,063.12 Roseberry Climbing Frame 9 7,441.00 826.78 Ridgeview Texas Climbing Frame 10 6,990.00 699 Selwood Picnic Table 9 489.92 54.44 I need the Totals of qty column and gross column ``` Many thanks Jason

Original source