SUM total - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SUM total

Author

Topic

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-28 : 23:22:05

My method of sum total


SELECT	t.st_code, m.st_desc, m.unit, sum(t.qtt_out) as qtt_out, sum(t.quantity2) as quantity2, sum(t.qtt_out-t.quantity2) as b_orderqty
FROM	ord_trx t left outer join st_mast m on m.st_code=t.st_code 
WHERE	(t.trx_type='SO' or t.trx_type='SOX') and 
		(t.do_date>='20070101' and t.do_date<'20080101') and 
		(t.st_code>='' and t.st_code<='zzzzzzzz')
GROUP BY t.st_code, m.st_desc, m.unit
UNION
SELECT	NULL, NULL, 'TOTAL', sum(t.qtt_out) as qtt_out, sum(t.quantity2) as quantity2, sum(t.qtt_out-t.quantity2) as b_orderqty
FROM	ord_trx t left outer join st_mast m on m.st_code=t.st_code 
WHERE	(t.trx_type='SO' or t.trx_type='SOX') and 
		(t.do_date>='20070101' and t.do_date<'20080101') and 
		(t.st_code>='' and t.st_code<='zzzzzzzz')
ORDER BY t.st_code

As you can see, i needed a union to find the total, is there any more efficient method to achieve the result?
(Without using reporting software)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-28 : 23:58:47

see http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=127637

Alternatively, since your GROUP BY is only single column you can do this.

use derived table and place the LEFT JOIN to st_mast outside and use WITH ROLLUP on the GROUP BY


SELECT	t.st_code, m.st_desc, 
        unit = case when t.st_code is null then 'TOTAL' else m.unit end, 
        t.qtt_out, t.quantity2, t.b_orderqty
FROM	(
		SELECT	t.st_code,  --m.st_desc, m.unit, 
			sum(t.qtt_out) 			as qtt_out, 
			sum(t.quantity2)		as quantity2, 	
			sum(t.qtt_out-t.quantity2) 	as b_orderqty
		FROM	ord_trx t 
		WHERE	(t.trx_type='SO' or t.trx_type='SOX') 
		and 	(t.do_date>='20070101' and t.do_date<'20080101') 
		and 	(t.st_code>='' and t.st_code<='zzzzzzzz')
		GROUP BY t.st_code --, m.st_desc, m.unit
		WITH ROLLUP
	) t            -- this is a derived table
	LEFT OUTER JOIN st_mast m 	on m.st_code = t.st_code 
ORDER BY t.st_code