asked on

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%

amit_g

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

edrz01

ASKER

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

ASKER CERTIFIED SOLUTION

amit_g

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

edrz01

ASKER

Great solution! Worked as expected and I appreciate the rapid response. Thank you!