SQL select column multiple times with different conditions

Question

I have a table with Incidents that contains column for number injured in the incident and column for number killed in that incident. Incident table is linked with Participants table where you can find a column for age of the person and there is another table ParticipantTypes with column for type(man(id = 1), woman(id = 2) for example). So, I want to retrieve column number injured, column number killed where type is a man and next to them column number injured, column number killed where type is a woman, all group by age.

select    
ip.age as age, 
sum(i.number_injured) as injured,
sum(i.number_killed) as killed
from Incidents i
inner join Participants ip on i.id = ip.incident_id
inner join ParticipantTypes ipt on ip.type_id = ipt.id
where ipt.id = 1
group by ip.age

the following query works and retrieve results only for ParticipantTypes.type = 1 (man)

I want it something like

select    
ip.age as age, 
sum(i.number_injured) as injured where ipt.id = 1,
sum(i.number_killed) as killed where ipt.id = 1, 
sum(i.number_injured) as injured where ipt.id = 2,
sum(i.number_killed) as killed where ipt.id = 2, 
from Incidents i
inner join Participants ip on i.id = ip.incident_id
inner join ParticipantTypes ipt on ip.type_id = ipt.id
group by ip.age

score 2 · Answer 1 · answered Sep 12 '15 at 09:27

select    
    ip.age as age, 
    sum(case when ipt.id=1 then i.number_injured    else 0 end)     as injured_man,
    sum(case when ipt.id=1 then i.number_killed     else 0 end)     as killed_man,
    sum(case when ipt.id=2 then i.number_injured    else 0 end)     as injured_woman,
    sum(case when ipt.id=2 then i.number_killed     else 0 end)     as killed_woman
 from 
    Incidents i
inner join 
    Participants ip on i.id = ip.incident_id
inner join 
    ParticipantTypes ipt on ip.type_id = ipt.id
group by ip.age

Example result:

age         injured_man killed_man  injured_woman killed_woman 
----------- ----------- ----------- ------------- ------------ 
         30          10           1             3            1 
         31          12           1             1            6 
         32          14           2             4            4

@Rohit Gaikwad is faster than me I guess :) – Burcin Sep 12 '15 at 09:28 — Burcin, Sep 12 '15 at 09:28

Sateesh Pagolu · Answer 2 · 2015-09-12T09:30:16.140

1

You are filtering out woman records with ipt.id = 1 condition both in inner join and where.

Also, you are grouping by age which is not something you want, as i understood from question. So, your final query should be.

EDIT: As per the updated details in question, following is the query

select
    ip.age,
    sum(case when ipt.id =1 then i.number_injured else 0 end) as men_injured,
    sum(case when ipt.id =1 then i.number_killed else 0 end) as men_killed,
    sum(case when ipt.id =2 then i.number_injured else 0 end) as women_injured,
    sum(case when ipt.id =2 then i.number_killed else 0 end) as women_killed
from Incidents i
       inner join Participants ip on i.id = ip.incident_id
       inner join ParticipantTypes ipt on ip.type_id = ipt.id
group by ip.age

edited Sep 12 '15 at 09:30

answered Sep 12 '15 at 08:24

Sateesh Pagolu

9,282
2
30
48

ipt.id = 1 in inner join was print mistake and yes I was aware about the on in where clause. Group by age is a must, lets rewrite it like : select ip.age as age, sum(i.number_injured) as injured where ipt.id = 1, sum(i.number_killed) as killed where ipt.id = 1, sum(i.number_injured) as injured where ipt.id = 2, sum(i.number_killed) as killed where ipt.id = 2, from Incidents i inner join Participants ip on i.id = ip.incident_id inner join ParticipantTypes ipt on ip.type_id = ipt.id group by ip.age – Antoniy Milenkinski Sep 12 '15 at 09:01
@AntoniyMilenkinski : updated answer based on the updates to the quesiton. – Sateesh Pagolu Sep 12 '15 at 09:33

score 1 · Answer 3 · answered Sep 12 '15 at 09:01

select    
ip.age as age, 
sum(if(ipt.id=1,i.number_injured,0)) as injured_by_man,
sum(if(ipt.id=1,i.number_killed,0) as killed_by_man,
sum(if(ipt.id=2,i.number_injured,0)) as injured_by_woman,
sum(if(ipt.id=2,i.number_killed,0)) as killed_by_woman

from Incidents i
inner join Participants ip on i.id = ip.incident_id
inner join ParticipantTypes ipt on ip.type_id = ipt.id
group by ip.age

Note: Above mentioned code is not tested. It may contain small syntactic error.

score 1 · Accepted Answer · answered Sep 12 '15 at 09:17

Try this....

select    
ip.age as age, 
sum(case when ipt.id=1then i.number_injured else 0                
end) as injured_by_man,
sum(case when ipt.id=1then i.number_killed else 0  
end) as killed_by_man,
sum(case when ipt.id=2 then i.number_injured else 0 
end) as injured_by_woman,
sum(case when ipt.id=2 then i.number_killed else 0 
end) as killed_by_woman

from Incidents i
inner join Participants ip on i.id = ip.incident_id
inner join ParticipantTypes ipt on ip.type_id = ipt.id
group by ip.age

SQL select column multiple times with different conditions

4 Answers4