• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

GROUP BY multiple fields, to include all possible grouped permutations, even non matching NULLS

Hi,

I have a table (below) for which I need to get a count of the ClientRef numbers, grouped by the two fields Age_Group_FK and VisitReason_FK. The grouping fields obviously join to the two other tables shown. Now, here's the bit I can't do: I want ClientRef counts of zero for all permutations not shown,  the important point being that I need to get all the permutations of Age_Group vs VisitReason. I have entirely made this example up, but it serves for the purposes of illustration!

ClientRef  Age_Group_FK VisitReason_FK
---------------------------------------------------
B1                2                     4
B2                2                     3
B3                3                     1
B4                2                     3
B5                2                     4
B6                2                     3
B7                2                     3
B8                1                     1

Age_Group_ID   Age_Group_Name
--------------------------------------------
1                             Under 15                
2                             16-24
3                             25-40
4                             Over 40

VisitReason_ID    Reason_Name
----------------------------------------
1                         Buy Magazine    
2                         Buy Groceries
3                         Goods Delivery
4                         Buy Alcohol
5                         Buy Pet Food

As you can see there'll need to be 20 (5 X 4) groupings in the final result, with many of them zero of course.

Thanks in advance
Iain
0
iaing1000
Asked:
iaing1000
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the following code should do it:
select ag.Age_Group_Name, vr.Reason_Name, sum(case when cr.Age_Group_FK is null then 0 else 1 end) as count_value
from Age_Group ag
cross join VisitReason vr
left join ClientRef_Table cr
  on cr.Age_Group_FK = ag.Age_Group_ID
 and cr.VisitReason_FK = vr.VisitReason_ID
group by ag.Age_Group_Name, vr.Reason_Name

Open in new window

0
 
iaing1000Author Commented:
Hi,

Yes, that's looking good, but in some instances there will be a <NULL> value in the client table and it won't have a lookup in any corresponding table.

For instance, if there is a Client for whom Age_Group_FK is 3 and VistReason_FK is stored as <NULL>, then I need an extra column in the groupings for the grouping of Age_Group 25-40 against <NULL> for VisitReason.

Currently, the way it does it is by providing an extra grouping with <NULL> in BOTH Age_Group and VisitReason columns. In fact, if there is a <NULL> value in any or either of the grouped columns they all get counted together as <NULL>,<NULL> for both grouped fields. Unfortunately, putting a NULL value in the lookup tables is not possible.

Any ideas?

Thanks,
Iain
0
 
iaing1000Author Commented:
Hi ,

I'm not sure if you're around at the moment and so I'll give you the points anyway...it did work like a dream, but I need the NULL stuff doing in the next hour or so and so will repost that as a separate question.

Thanks
Iain
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now