Need SQL Case Statement Help

frogman22
frogman22 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ephraim WangoyaSoftware Engineer

Commented:
What field contains the values you wish to sum up?

Author

Commented:
sourceform
Software Engineer
Commented:

That field does not contain numbers so you cant do a sum on it, you need numeric fields for SUM.

If you just want to do a count, change your query to


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

Open in new window

Author

Commented:
That was it!!!! Thanks for the help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial