Retrieving totals using the By group

Question

I'm currently trying to talk more about SQL, and I'm currently trying to run some simple sales reports using the SUM , COUNT , AVG and GROUP BY functions in the SQL Server 2008 database. I was able to get the total, quantity, and average each group line by line.

How to get the total amount of the whole group in rows?

SQL:

 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 a ROLLUP statement that will add a large summary line at the end of a result set. If you are looking for more complex aggregate totals, use ROLLUP or CUBE with a GROUP BY clause, such as the link provided by @MartinSmith or WITH ROLLUP Aggregation

 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 for future reference only - you can also get more control over the grouping process 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), () ) 

Thus, the result will have both lines grouped by txt_orditems_pname, and without grouping at all. You can specify more sets of groupings, i.e. the request for the average salary in the department and team also returned, with totals for the department and the whole company.

Answer 3

Wrap your selection in another selection and summarize the columns.