CODEWITHSUNDEEP
sqlsql-server
Andrey
Andrey

Reputation: 1759

Query with count by groups

I have groups of categories and group of ethnicity i need wright query for report which will looks like this enter image description here

right now I have query which can count by clients ID and ethnicity. However he is trick also client table has Hispanic field (bit 0 false 1 true). Client can have any ethnicity but in the same time he/she can be Hispanic. my query right now can count client but for Hispanic it just list count and not divide it by ethnicity 

select ethnicity, COUNT (c.EthnCode) as '(A) tottal Numbers of participamts by Rase', 
(select COUNT (Hispanic) from clients c JOIN ethnicity e
ON c.EthnCode = e.EthnCode
where c.EthnCode in ('N','A','B','P','W','NW','AW','BW','BN') and Hispanic =1 )as '(B)Number of Hispanic or Latino Participants Reported in Column A by Race'
from  Clients c
JOIN ethnicity e
ON c.EthnCode = e.EthnCode
where c.EthnCode in ('N','A','B','P','W','NW','AW','BW','BN')
group by ethnicity

Also If some ethnicity do not have perticipiants it just not showing it in result but i need to show 0 for that ethnicity. here is the result of my query enter image description here

As you can see Hispanic not divided by category . Need Help trying to solve this problem second day still no have any success

Upvotes: 0

Views: 364

Answers (1)

Kyle Hale
Kyle Hale

Reputation: 8120

Try this:

select ethnicity, COUNT (c.EthnCode) as '(A) tottal Numbers of participamts by Rase', 
sum(case when Hispanic=1 then 1 else 0 end) as '(B)Number of Hispanic or Latino Participants Reported in Column A by Race'
from  Ethnicity E
LEFT JOIN Clients C
ON c.EthnCode = e.EthnCode
where e.EthnCode in ('N','A','B','P','W','NW','AW','BW','BN')
group by ethnicity

The SUM(CASE...) serves as a kind of "sub-count" within the actual count.

UPDATE:

In order to lump all the other codes into an "Other Multiples" category, do this:

select 
    case 
    when e.EthnCode in ('N','A','B','P','W','NW','AW','BW','BN') then ethnicity
    else 'Other Multiples' 
    end as ethnicity, 
    COUNT (c.EthnCode) as '(A) tottal Numbers of participamts by Rase', 
    sum(case when Hispanic=1 then 1 else 0 end) as '(B)Number of Hispanic or Latino Participants Reported in Column A by Race'
from  Ethnicity E
LEFT JOIN Clients C
ON c.EthnCode = e.EthnCode
group by case 
    when e.EthnCode in ('N','A','B','P','W','NW','AW','BW','BN') then ethnicity
    else 'Other Multiples' 
    end

This assumes that all the ethncodes in Ethnicity that aren't in your hardcoded statement are multiples.

Upvotes: 2

Related Questions