CODEWITHSUNDEEP
sql-servert-sqlcase
frangopop
frangopop

Reputation: 41

SQL multiple conditions CASE WHEN and or

I have joined two tables (tbl1 and tbl2). AnimalNo is present in both tables, but it is the primary key of tbl1 and can occur multiple times in tbl2 (if the AnimalNo is associated with multiple ConditionID)

tbl1

AnimalNo  |   KillDate
1         |   01/01/2019
2         |   01/01/2019
3         |   01/01/2019
4         |   01/01/2020
5         |   01/01/2020

tbl2

AnimalNo  |   ConditionID
1         |   1
1         |   2
2         |   1
3         |   1
3         |   2 
4         |   1
5         |   1
5         |   2

I would like a table where I get a count of the AnimalID which had each condition and the count of the AnimalNo which had both ConditionID 1 & 2, in the above example:

Year  |  N_killed   |  Type1   | Type2    | Both 
2019  |  3          | 3        | 2        |  2      
2020  |  2          | 2        | 1        |  1

I have this query which is successful at telling me the number of AnimalNo with each individual ConditionID but I would like to add the last column called Both which has a count of the AnimalNo which have both ConditionID 1 & 2

SELECT
  
  DATEPART(year, tbl1.KillDate) AS KillYear,
                COUNT(Distinct tbl1.AnimalNo) AS N_Killed,
                COUNT(CASE WHEN tbl2.ConditionId =1 THEN 1 END) AS Type1,
                COUNT(CASE WHEN tbl2.ConditionId =2 THEN 1 END) AS Type2
FROM            tbl1 LEFT JOIN
                         tbl2 ON tbl1.AnimalNo = tbl2.AnimalNo
WHERE YEAR(tbl1.KillDate) >=2012
GROUP BY DATEPART(year, KillDate)
ORDER BY DATEPART(year, KillDate)

Upvotes: 1

Views: 76

Answers (2)

AhmedHuq
AhmedHuq

Reputation: 469

Try this:

declare @tbl1 table
(
AnimalNo int
,KillDate date
)

insert into @tbl1(AnimalNo, KillDate)
select 1, '2019-01-01' union
select 2, '2019-01-01' union
select 3, '2019-01-01' union
select 4, '2020-01-01' union
select 5, '2020-01-01'

declare @tbl2 table
(
AnimalNo int
,ConditionID int
)

insert into @tbl2(AnimalNo, ConditionID)
select 1,1 union
select 1,2 union
select 2,1 union
select 3,1 union
select 3,2 union
select 4,1 union
select 5,1 union
select 5,2


select
year(k.KillDate) as [Year]
,count(distinct k.AnimalNo) as N_Killed
,sum(c.Type1)
,sum(c.Type2)
,sum(case when c.Type1 = 1 and c.Type2 = 1 then 1 else 0 end) as Both
from

@tbl1 k

JOIN
(
select 
c.AnimalNo
,max(case when c.ConditionID = 1 then 1 else 0 end) as Type1
,max(case when c.ConditionID = 2 then 1 else 0 end) as Type2
from
@tbl2 c
group by c.AnimalNo
) c
on k.AnimalNo = c.AnimalNo
group by year(k.KillDate)

Upvotes: 1

Shahbaz
Shahbaz

Reputation: 178

I think this is that you want

SELECT
  
  DATEPART(year, tbl1.KillDate) AS KillYear,
                COUNT(Distinct tbl1.AnimalNo) AS N_Killed,
                COUNT(CASE WHEN tbl2.ConditionId =9 THEN 1 END) AS Type1a,
                COUNT(CASE WHEN tbl2.ConditionId =75 THEN 1 END) AS Type1b,
                COUNT(CASE WHEN tbl2.ConditionId =23 THEN 1 END) AS Type2a,
                COUNT(CASE WHEN tbl2.ConditionId =81 THEN 1 END) AS Type2b,
                COUNT(CASE WHEN tbl2.ConditionId IN (9,75) OR tbl2.ConditionId IN (23,81) BothTypes
FROM tbl1 LEFT JOIN
     tbl2 ON tbl1.AnimalNo = tbl2.AnimalNo
WHERE YEAR(tbl1.KillDate) >=2012
GROUP BY DATEPART(year, KillDate)
ORDER BY DATEPART(year, KillDate)

Upvotes: 1

Related Questions