adding a grand 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

General SQL Server Forums

New to SQL Server Programming

adding a grand total

Author

Topic

tariq2
Posting Yak Master

125 Posts

Posted - 2011-04-11 : 10:18:43

The following query yields three columns
L5Account,L5Sub,Amount

However, I require an additional row at the bottom
which gives the grand total.

I have tried adding compute sum(amount) to the code
but without success.
Thanks in advance

select b.l5account,b.l5sub,sum(a.amount) as Amount from f_is_bu a inner join biprod.dbo.D_Accounts b
on a.AccountKey = b.AccountKey
where a.BUKey in (select bukey from biprod.dbo.D_BusinessUnits where BusinessUnitID = '02811')
and a.PeriodKey >96
group by b.L5Account,b.L5Sub

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-11 : 10:25:50

SELECT IsNull(b.l5account, 'Total'), IsNull(b.l5sub,'Total'), SUM(a.amount) AS Amount
FROM f_is_bu a
INNER JOIN biprod.dbo.D_Accounts b ON a.AccountKey = b.AccountKey
WHERE a.BUKey IN (SELECT bukey FROM biprod.dbo.D_BusinessUnits WHERE BusinessUnitID = '02811')
AND a.PeriodKey >96
GROUP BY b.L5Account,b.L5Sub WITH ROLLUP

tariq2
Posting Yak Master

125 Posts

Posted - 2011-04-11 : 12:01:18

Thank you , very helpful

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources