Solved

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

Posted on 2007-11-29
3
175 Views
Last Modified: 2010-04-30
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
Comment
Question by:iaing1000
  • 2
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 20377632
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
 

Author Comment

by:iaing1000
ID: 20411280
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
 

Author Comment

by:iaing1000
ID: 20412590
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now