Group by and total of the resultant records
-
SELECT dept_n0,SUM(hours)
FROM emp
GROUP BY dept_no
The above quesry gives the result
dept1 100
dept2 200
dept3 300
But I want a result set which includes a grand total too.
dept1 100
dept2 200
dept3 300
total 600
How can I do this in a single query without using a temporary table.
Thanks.
-
Change your GROUP BY to this: GROUP BY dept_no WITH ROLLUP
It just won't say total under deptX
-
Thank you very much for your help.
-
I don't know if this is acceptable performance wise in your case but it prints total on the last line
Select
CASE WHEN ID IS NULL THEN 'Total' ELSE CAST(ID AS VARCHAR(128)) END
, Sum(XType) from Sys.SysColumns
GROUP
BY ID WITH ROLLUP
-
Try This:
SELECT
CASE WHEN (GROUPING(so.sales_order_phase_id)=1) THEN
'All'
ELSE
CAST(so.sales_order_phase_id AS VARCHAR)
END sales_order_phase_id
,SUM(so.contract_value_local_price)
FROM dbo.bv_sales_order so
GROUP BY so.sales_order_phase_id WITH ROLLUP
The GROUPING keyword adds the name for the extra row (Do a search for ROLLUP in BOL)
I hope that helps...
-
Nice to learn that... and I would expect it to perform slightly better than my version
.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply