Link to home
Start Free TrialLog in
Avatar of frogman22
frogman22

asked on

Need SQL Case Statement Help

When using the following case statement I get results that = "Total Records" for the year in all monthly columns.
Please see attached results spreadsheet as example of query results.
I do not understand what the problem is.

If I change "count" to "sum" I get the following error-
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'sourcefrom' to data type int.

begindate = datetime
sourcerfrom = varchar(20)

select datepart(year,begindate) as Years,sourcerfrom,
count(case when datepart(month,begindate)= 1 then sourcerfrom else 0 end) as January,
count(case when datepart(month,begindate)= 2 then sourcerfrom else 0 end) as February,
count(case when datepart(month,begindate)= 3 then sourcerfrom else 0 end) as March,
count(case when datepart(month,begindate)= 4 then sourcerfrom else 0 end) as April,
count(case when datepart(month,begindate)= 5 then sourcerfrom else 0 end) as May,
count(case when datepart(month,begindate)= 6 then sourcerfrom else 0 end) as June,
count(case when datepart(month,begindate)= 7 then sourcerfrom else 0 end) as July,
count(case when datepart(month,begindate)= 8 then sourcerfrom else 0 end) as August,
count(case when datepart(month,begindate)= 9 then sourcerfrom else 0 end) as September,
count(case when datepart(month,begindate)= 10 then sourcerfrom else 0 end) as October,
count(case when datepart(month,begindate)= 11 then sourcerfrom else 0 end) as November,
count(case when datepart(month,begindate)= 12 then sourcerfrom else 0 end) as December,
count(*)as Total
from db1.dbo.tbl_1
group by datepart(year,begindate),sourcerfrom
order by sourcerfrom,years

case-statement-results.xlsx
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

What field contains the values you wish to sum up?
Avatar of frogman22
frogman22

ASKER

sourceform
ASKER CERTIFIED SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That was it!!!! Thanks for the help