Question about "group by" in SP

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

Question about "group by" in SP

Author

Topic

wentihenduo
Starting Member

8 Posts

Posted - 2013-06-27 : 05:16:31

Here is my table A
(
month int,
sale int
)

there are 12 months, I need to get each month's sale summary in a stored procedure, and save these data into another table.

Now, I can only write this kind of statements:
declare @m1 int
declare @m2 int
...
declare @m12 int

select @m1 = SUM(sale) from A where month = 1 group by month
...
select @m12 = SUM(sale) from A where month = 12 group by month

You see, I have to write 12 statements, it is so... I mean, is there any way to get these 12 months summary in one statement?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 05:20:39

yep. you can use group by for that and get them in single statement


SELECT [month],
SUM(sale) AS sale
FROM A
GROUP BY [month]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wentihenduo
Starting Member

8 Posts

Posted - 2013-06-27 : 05:30:14

hi visakh16! Thanks for your reply, sorry I did not described my question well.
I mean, I need to save each month's summary into @m1, @m2 ... @m12
so, after this statement, how can I set value to @m1 ? do I set this after the statement or in this query? and how?

SELECT [month],
SUM(sale) AS sale
FROM A
GROUP BY [month]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 05:34:02

for that best way is this


SELECT @m1=[1],
@m2=[2],
..,
@m12=[12]
FROM A a
PIVOT(SUM(sale) FOR [month] IN ([1],[2],[3],[4],[5],[6],...,[12]))p

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wentihenduo
Starting Member

8 Posts

Posted - 2013-06-27 : 07:49:01

Thanks a lot! Exactly what I want! Thank you!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 09:35:50

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources