Say I have a table, and one of the column is GroupName. Each row can have one of three values for this column: A, B, or C. If I want to query how many objects of each group are in my database, I can run something like this:
SELECT COUNT(*) c, GroupName
FROM MyTable
GROUP BY GroupName
Here's my problem: if, for example, I have 100 objects in the table, and 75 are in A 25 in B and 0 in C, if I run this query it will return this:
GroupName c
--------------
A 75
B 25
However, I am trying to use this query to populate an SSRS table to show the amounts, and this table needs to show all possible groups, not just the ones actually used. In this example, I would still need a third row in my table for C. If the row was there I know how to put in a 0, eg =IIf(Fields!c.Value = Nothing, "0", Fields!c.Value), but in the above example it doesn't even create a row. How can I assure that a row will be created for each value, even if said value is not used?
To even know you have a group C, that must be in one of your tables, so you'll need to do something like this:
select X.c, G.GroupName
from
groups G
outer apply (
SELECT COUNT(*) c, GroupName
FROM MyTable
GROUP BY GroupName
) X on X.GroupName = G.GroupName