SUBTOTAL Functions ? (Seems like an easy one ...)

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

SUBTOTAL Functions ? (Seems like an easy one ...)

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2011-07-21 : 11:57:13

Hi All - I have a "Sales" table which has sales totals for each store.

CHAIN ADDRESS SALES
TARGET 12 MAIN ST $1,003.07
TARGET 4079 BROADWAY $10,101.49
TARGET 749 LINCOLN AVE $5,978.02
WALGREENS 123 BIRCH $543.99
WALGREENS 9779 SPARROW $54.97
WALGREENS 49 BAYWOOD $67.97

SELECT [CHAIN], [ADDRESS], SUM([SALES]) FROM SALESTABLE
GROUP BY [CHAIN], [ADDRESS]

I would like the results to subtotal each "chain", as well as display the address as well, so that results are displayed as follows:

TARGET 12 MAIN ST $1,003.07
TARGET 4079 BROADWAY $10,101.49
TARGET 749 LINCOLN AVE $5,978.02
TARGET TOTAL $17082.58

WALGREENS 123 BIRCH 543.99
WALGREENS 9779 SPARROW $54.97
WALGREENS 49 BAYWOOD $67.97
WALGREENS TOTAL $666.93

GRAND TOTAL $17749.51

Can anyone advise on how this can be done? THANKS!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-21 : 12:02:43

use WITH CUBE to get subtotals. alternatively if its a reporting application (SSRS) you can get it very easily by using a group footer

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pduffin
Yak Posting Veteran

68 Posts

Posted - 2011-07-22 : 17:34:47

As long as Sales are not negative this should work for you

select chain,[ADDRESS],sum(sales) as Sales
from #SALESTABLE
group by chain,[ADDRESS]

union all

select chain,rtrim(Chain) + ' Total',sum(Sales)
from #SALESTABLE
group by chain
order by chain,sum(Sales)

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-23 : 11:05:03

Look at the ROLLUP option in GROUP BY

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources