CODEWITHSUNDEEP
mysqlsqlgroup-bycase
beck777
beck777

Reputation: 49

Use "CASE" command on a GROUP BY level

I am trying to get 5 columns, 1st column being the evaluation unit (EU), but in the next 4 columns I want to count how many households (distinct INSTANCE_ID_HOUSE) meet specific criteria, but the data is stored by resident. Here are the 4 queries I use (I basically just want to combine them into one query):

SELECT EU, COUNT( DISTINCT INSTANCE_ID_HOUSE ) FROM SENEGAL_CLEAN
WHERE CLUSTER_COMPLETE>0
GROUP BY EU
ORDER BY EU ASC

SELECT COUNT( DISTINCT INSTANCE_ID_HOUSE ) FROM SENEGAL_CLEAN
WHERE CLUSTER_COMPLETE>0 AND (W1_DRINK_SOURCE=1 OR W1_DRINK_SOURCE=4)
GROUP BY EU
ORDER BY EU ASC

SELECT COUNT( DISTINCT INSTANCE_ID_HOUSE ) FROM SENEGAL_CLEAN
WHERE CLUSTER_COMPLETE>0 AND (W2_GET_DRINK_WATER=1 OR W2_GET_DRINK_WATER=2)
GROUP BY EU
ORDER BY EU ASC

SELECT COUNT( DISTINCT INSTANCE_ID_HOUSE ) FROM SENEGAL_CLEAN
WHERE CLUSTER_COMPLETE>0 AND (S2_SEE_LATRINE=1 OR S2_SEE_LATRINE=2)
GROUP BY EU
ORDER BY EU ASC

Upvotes: 1

Views: 81

Answers (2)

Reza
Reza

Reputation: 19843

Try something like this :

Select S.EU,t1.c1 , t2.c2, t3.c3, t4.c4 
From SENEGAL_CLEAN S
inner join(

SELECT EU, COUNT( DISTINCT INSTANCE_ID_HOUSE ) c1 FROM SENEGAL_CLEAN
WHERE CLUSTER_COMPLETE>0
GROUP BY EU
)t1 on t1.Eu= s.Eu

inner join(
SELECT EU,COUNT( DISTINCT INSTANCE_ID_HOUSE ) c2 FROM SENEGAL_CLEAN
WHERE CLUSTER_COMPLETE>0 AND (W1_DRINK_SOURCE=1 OR W1_DRINK_SOURCE=4)
GROUP BY EU
)t2 on t2.Eu= s.Eu

inner join(
SELECT Eu,COUNT( DISTINCT INSTANCE_ID_HOUSE ) c3 FROM SENEGAL_CLEAN
WHERE CLUSTER_COMPLETE>0 AND (W2_GET_DRINK_WATER=1 OR W2_GET_DRINK_WATER=2)
GROUP BY EU
)t3 on t3.Eu= s.Eu

inner join(
SELECT Eu,COUNT( DISTINCT INSTANCE_ID_HOUSE ) c4 FROM SENEGAL_CLEAN
WHERE CLUSTER_COMPLETE>0 AND (S2_SEE_LATRINE=1 OR S2_SEE_LATRINE=2)
GROUP BY EU
)t4 on t4.Eu = s.Eu

SqlFiddle:http://sqlfiddle.com/#!9/90b33/1

Upvotes: 0

Andrew
Andrew

Reputation: 8703

You can use case statements for your counts:

    select
    eu,
    count (distinct case when cluster_complete > 0 then instance_id_house else null end) as YourAliasHere,
    count (distinct when cluster_complete > 0 and (W1_DRINK_SOURCE=1 OR W1_DRINK_SOURCE=4) then instance_id_house else null end) as YourNextAlias\
    ...
FROM SENEGAL_CLEAN
group by EU

So you'll count each instance_id_house that matches your criteria in each of the case statements. I think you can just leave your where clause out (based on what you're currently showing).

Upvotes: 2

Related Questions