Getting Grand Totals using Group By

Question

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

Answer 1

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

Answer 2

I know this is an old question, but just for future reference - you can also gain more control over the grouping process by using GROUPING SETS. For example:

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,
    COALESCE(dbo.tbl_orderitems.txt_orditems_pname, 'TOTAL')
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 GROUPING SETS (
    (dbo.tbl_orderitems.txt_orditems_pname),
    ()
)

This way the result will have both rows grouped by the txt_orditems_pname, and with no grouping at all. You can specify more grouping sets, ie. a query for average salary in a department and team, with totals for department and the entire company also returned.