Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2007-11-29
3
Medium Priority
?
182 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library 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.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

718 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