select count(Mon) as total_mon, count(Tue) as total_tue, count(Wed) as total_wed from table group by name
That doesn't seem to give an accurate answer. Count(mon) just seems to return the number for all rows in the column. So, in this case for Mon, instead of a count of 2 being returned (1 for OFF and 1 for VAC), it returns 3, as there are 3 rows. Any ideas? Anyone?
I was thinking that perhaps I could somehow assign a value of 1 to each in the columns, and then perform a sum.
Yes, you could use a CASE statement to return 1 or 0. Then wrap it in a SUM(..).
SELECT SUM( CASE WHEN ColumnName IS NULL THEN 0 ELSE 1 END ) AS TotalNumber ....
1 person found this helpful
Hmm, are you sure you have NULL values there ? (maybe you have empty
strings instead of NULL)
From the documentation
(http://msdn.microsoft.com/en-us/library/ms175997.aspx) : Count(mon)
is equivalent to Count(ALL mon) and it counts the number of non-null
You were right Mack. I did have empty strings in the database. I did modify the other answer given as so, which solved the problem:
SUM(CASE WHEN column = '' THEN 0 ELSE -1 END)
Thanks to both of you for your help.