Return 4 separate counts for single column in SSRS DataSet

Question

I want to provide 5 counts to SSRS from a conditional count on a column. For instance, suppose the column held the color of a product -- green ,blue, red and yellow. What I would like to do is return the count of each in a single query.

Although I can accomplish getting this done using a case statement:

Select
      COUNT(*) 'Count',
      case
            When Color = 'BL' then 'Blue
            When Color = 'RD' then 'Red
            When Color = 'YL' then 'Yellow
            When Color = 'GR' then 'Green
            Else 'All Others'
      End as Payment
From COLORS(NoLock)
Group by
      case
            When Color = 'BL' then 'Blue
            When Color = 'RD' then 'Red
            When Color = 'YL' then 'Yellow
            When Color = 'GRthen ‘Green’
            Else 'All Others'
      End

When I use the dataset is SSRS, all I get is the a single count. I don't want to create 4 dataset queries as I'm actually selection the records by the parameters start and end date and I would end up having 5 sets of date parameters.

I have edited your title. Please see, "Should questions include “tags” in their titles?", where the consensus is "no, they should not". Also, unlike forum sites, we don't use "Thanks", or "Any help appreciated", or signatures on Stack Overflow. See "Should 'Hi', 'thanks,' taglines, and salutations be removed from posts?. — John Saunders
I am not sure what seems to be the issue here, I just tested this query in SSRS and it produced 5 rows of data, one for each of the colors and then the all others value. Can you clarify what you are attempting to do? — Taryn

alzaimar alzaimar · Accepted Answer · 2012-11-05T20:04:36

This should do the trick

select count (*) as Total,
   sum (case when color='BL' then 1 else 0 end) as BlueTotal,
   sum (case when color='RD' then 1 else 0 end) as RedTotal,
   sum (case when color='YL' then 1 else 0 end) as YellowTotal,
   sum (case when color='GR' then 1 else 0 end) as GreenTotal
from Colors

Return 4 separate counts for single column in SSRS DataSet

1 Answers