Group, Count Dilemma

Question

Post reply

Group, Count Dilemma

tygun

SSCrazy

Points: 2381
More actions
December 27, 2002 at 1:44 pm

#79423

I have a table like the following example
Region Store Audit
1 1 55
1 1 56
1 2 63
1 2 64
1 2 65
1 3 null
My problem is this, I do not want to use any functions or split table up to accomplish the following.
I want a query that will return the following
Region stores_in_Region No_Audited_Stores
1 3 2
Group by region
Count by Distinct Stores
???? Audit -- What do I do to get my number for the audited stores. I you look above you'll see we have region 1, we have 3 stores, but only 2 have audits and store 3 is null. How can I do select to get my desired results.
Select region, count(distinct store), "??What about Audit"
from my_table
group by region
I hope someone can help. Please ask questions if you need to. I'm under a little pressure so if someone has a second to help me it would be Tremedously appreciated.
Thanks again.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

mromm SSCarpal Tunnel Points: 4301 More actions · Answer 1

So, what is the exact question (in English) your query needs to answer?

Edited by - mromm on 12/27/2002 2:24:25 PM

NPeeters SSChampion Points: 12536 More actions · Answer 2

I think this might help you on your way, maybe there is some special data this query does not handle properly.



SELECT T.region, 

       COUNT(DISTINCT store),

       (SELECT COUNT(DISTINCT store)

        FROM my_table T2 

        WHERE NOT(audit IS NULL)

          AND T2.region = T.region)

FROM my_Table T

GROUP BY T.region