Best way to do query

What is the best way to do this?

I have a query which groups and counts data. I also need to 'filter' that same data so I can get a percentage between the two.

For instance, query1 would return the total:

Group        Count
Data            95
System       426
Mkt           1560
Sales          264

I then need query1 to be the filtered data:
Group        Count
Data            13
System        17
Mkt             12
Sales            1

So the final results would something like
Data            14%
System        4%
Mkt             1%
Sales            1%
edrz01Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
amit_gConnect With a Mentor Commented:
Use Left Outer Join instead of Inner Join
Select Q1.Group, Q1.Count * 100 / Q2.Count From
(Select Group, Count... this is Query1) Q1
left outer join (Select Group, Count... this is Query1) Q2 on Q1.Group = Q2.Group

Open in new window

0
 
amit_gCommented:
Select Q1.Group, Q1.Count * 100 / Q2.Count From
(Select Group, Count... this is Query1) Q1
inner join (Select Group, Count... this is Query1) Q2 on Q1.Group = Q2.Group
0
 
edrz01Author Commented:
amit_g,

Thanks, that worked like a charm.

just one follow up question, I see from my previous results that I might have some Q1 (All) that might not show up because they had nothing in Q2. How can I account for those as well?

For example Q1:
Group        Count
Data            95
System       426
Mkt           1560
Sales          264
Misc           122
Other          97

I then need Q2 to be the filtered data:
Group        Count
Data            13
System        17
Mkt             12
Sales            1

So the final results would something like
Data            14%
System        4%
Mkt             1%
Sales            1%
Misc           No Data <--- Did not show up in Q2
Other         No Data <--- Did not show up in Q2
0
 
edrz01Author Commented:
Great solution! Worked as expected and I appreciate the rapid response. Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.