how do I get a sum over month in SQL server ? 1

[bcdixit]

Currently I am running this query to get a sum for each day.

select Activity,
act_a = sum(case tintactivitytype when 3 then 1 else 0 end),
act_b = sum(case tintactivitytype when 4 then 1 else 0 end)
from dbo.tblpostpaid
group by activity
order by activity

the column activity is a 'datetime'

part of my output is as follows.

activity act_a act_b
2007-03-05 23:31:27.000 0 1
2007-03-23 17:38:49.000 0 0
2007-03-22 02:36:00.000 0 1
2007-03-17 11:48:20.000 0 0
2007-03-12 15:49:11.000 0 0
2007-03-25 09:56:54.000 0 0
2007-04-01 13:39:47.000 0 1
...
...
...

I want to get something like this..
activity act_a act_b
2007-03 8 9
2007-02 6 5
2007-01 12 10
2006-12 11 8
...
* the values in act_a and act_b are just sample values ..
basically I want to sum the act_a and act_b values and report it over a month to month basis?


are there any special function in sqlserver that can help me do this?

 

[ptheriault]

I think if you group by datepart(mm,activity) it will work.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

Sorry my syntax was wrong.

GROUP BY DATEPART(Month, activity)

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

I had a very big mistake. My code only worked for one year.

Here is a revised version.

[COLOR=blue]DECLARE[/color] @table [COLOR=blue]table[/color] (activity [COLOR=#FF00FF]datetime[/color], act_a [COLOR=blue]int[/color], act_b [COLOR=blue]int[/color])

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @table [COLOR=blue]Values[/color]([COLOR=red]'2007-03-05 23:31:27.000'[/color], 0, 1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @table [COLOR=blue]Values[/color]([COLOR=red]'2007-03-23 17:38:49.000'[/color], 0, 1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @table [COLOR=blue]Values[/color]([COLOR=red]'2007-03-22 02:36:00.000'[/color], 0, 1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @table [COLOR=blue]Values[/color]([COLOR=red]'2006-03-17 11:48:20.000'[/color], 0, 1)
[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] @table [COLOR=blue]Values[/color]([COLOR=red]'2007-04-01 13:39:47.000'[/color], 0, 1)

[COLOR=blue]SELECT[/color] SUM(act_a) [COLOR=blue]as[/color] a
      ,SUM(act_b) [COLOR=blue]as[/color] b
[COLOR=blue]FROM[/color] @table
[COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] [COLOR=#FF00FF]DATEPART[/color]([COLOR=#FF00FF]MONTH[/color], Activity), [COLOR=#FF00FF]DATEPART[/color]([COLOR=#FF00FF]YEAR[/color], activity)

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[bcdixit]

thats fine...but how do I get it grouped by month and year
as in the example i have given in the original thread

here is the output from your solution
activity act_a act_b
1 25 26
2 35 38
3 60 24
4 76 45
5 41 11

 

[gmmastros]

Like this....

[COLOR=blue]select[/color] [COLOR=#FF00FF]year[/color](activity) [COLOR=blue]As[/color] [[COLOR=#FF00FF]Year[/color]], 
       [COLOR=#FF00FF]Month[/color](Activity) [COLOR=blue]As[/color] [[COLOR=#FF00FF]Month[/color]],
       act_a = sum([COLOR=blue]case[/color] tintactivitytype [COLOR=blue]when[/color] 3 [COLOR=blue]then[/color] 1 [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color]),
       act_b = sum([COLOR=blue]case[/color] tintactivitytype [COLOR=blue]when[/color] 4 [COLOR=blue]then[/color] 1 [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color])
[COLOR=blue]from[/color]   dbo.tblpostpaid
[COLOR=blue]group[/color] [COLOR=blue]by[/color] [COLOR=#FF00FF]year[/color](activity), [COLOR=#FF00FF]Month[/color](Activity)
[COLOR=blue]order[/color] [COLOR=blue]by[/color] [COLOR=#FF00FF]year[/color](activity), [COLOR=#FF00FF]Month[/color](Activity)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bcdixit]

thanks ptheriault
is there a way to combine the month and the year and order it based on the month and year?

 

[gmmastros]

Ok. Here's another way...

[COLOR=blue]select[/color] [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Month[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Month[/color], 0, Activity), 0) [COLOR=blue]As[/color] [ActivityMonth], 
       act_a = sum([COLOR=blue]case[/color] tintactivitytype [COLOR=blue]when[/color] 3 [COLOR=blue]then[/color] 1 [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color]),
       act_b = sum([COLOR=blue]case[/color] tintactivitytype [COLOR=blue]when[/color] 4 [COLOR=blue]then[/color] 1 [COLOR=blue]else[/color] 0 [COLOR=blue]end[/color])
[COLOR=blue]from[/color]   dbo.tblpostpaid
[COLOR=blue]group[/color] [COLOR=blue]by[/color] [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Month[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Month[/color], 0, Activity), 0)
[COLOR=blue]order[/color] [COLOR=blue]by[/color] [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Month[/color], [COLOR=#FF00FF]DateDiff[/color]([COLOR=#FF00FF]Month[/color], 0, Activity), 0)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

George,
You've got to be the fastest typer I've met!



I was almost finished when I decided to check to make sure nobody already posted.



- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[bcdixit]

thanks George and Paul for all your help.