How to count rows matching certain conditions within each group?

Question

I have more columns in my table vb10_cartridgedata but the following two columns are my columns of interest: NestID and FailCode. NestID can only contain numbers from 1 to 16. I am trying to count how many times did each NestID produced each FailCode, e.g. how often the FailCode=11 showed up for NestID =1, or how many times did the FailCode=4 showed up for NestID= 4 (for every nest).

NestID	FailCode
1	0
1	11
1	11
2	23
3	25
4	32
4	32

Conceptually, I am expecting a result similar to this:

NestID	Ch1	Ch2
1	count("FailCode"= 10) as "Ch1" where "NestID" =1	count("FailCode"=23) as "Ch2" where "NestID" =1
2	count("FailCode"= 10) as "Ch1" where "NestID" =2	count("FailCode"=23) as "Ch2" where "NestID" =2
3	count("FailCode"= 10) as "Ch1" where "NestID" =3	count("FailCode"=23) as "Ch2" where "NestID" =3
4	count("FailCode"= 10) as "Ch1" where "NestID" =4	count("FailCode"=23) as "Ch2" where "NestID" =4
5	count("FailCode"= 10) as "Ch1" where "NestID" =5	count("FailCode"=23) as "Ch2" where "NestID" =5
6	count("FailCode"= 10) as "Ch1" where "NestID" =6	count("FailCode"=23) as "Ch2" where "NestID" =6
7	count("FailCode"= 10) as "Ch1" where "NestID" =7	count("FailCode"=23) as "Ch2" where "NestID" =7
8	count("FailCode"= 10) as "Ch1" where "NestID" =8	count("FailCode"=23) as "Ch2" where "NestID" =8
9	count("FailCode"= 10) as "Ch1" where "NestID" =9	count("FailCode"=23) as "Ch2" where "NestID" =9
10	count("FailCode"= 10) as "Ch1" where "NestID" =10	count("FailCode"=23) as "Ch2" where "NestID"=10
11	count("FailCode"= 10) as "Ch1" where "NestID" =11	count("FailCode"=23) as "Ch2" where "NestID"=11
12	count("FailCode"= 10) as "Ch1" where "NestID" =12	count("FailCode"=23) as "Ch2" where "NestID"=12
13	count("FailCode"= 10) as "Ch1" where "NestID" =13	count("FailCode"=23) as "Ch2" where "NestID"=13
14	count("FailCode"= 10) as "Ch1" where "NestID" =14	count("FailCode"=23) as "Ch2" where "NestID"=14
15	count("FailCode"= 10) as "Ch1" where "NestID" =15	count("FailCode"=23) as "Ch2" where "NestID"=15
16	count("FailCode"= 10) as "Ch1" where "NestID" =16	count("FailCode"=23) as "Ch2" where "NestID"=16

So, I am expecting to see something like this:

NestID	Ch1	Ch 2
1	5	0
2	33	173
3	5	0
4	20	28
5	0	193
6	1	27
7	203	1
8	13	0
9	2	261
10	20	0
11	173	23
12	0	53
13	0	0
14	0	0
15	5	14
16	106	0

I've tried the following, but it's not working for me (please see the picture to see what I am getting):

SELECT "NestID",
"FailCode",
count ("FailCode"=23) as "CH1", count ("FailCode"=11) as "CH2"
FROM public.vb10_cartridgedata WHERE "FailCode" != 0 
group by 1, 2;

This picture is what I have attempted so far:

Answer 1

As suggested by a_horse_with_no_name

SELECT "NestID",
"FailCode",
count (*) FILTER (WHERE "FailCode"=23) as "CH1", 
count (*) FILTER (WHERE "FailCode"=11) as "CH2"
FROM public.vb10_cartridgedata WHERE "FailCode" != 0 
group by 1, 2;

Alternatively (e.g. for databases that do not support filtered aggregates) you could use something like this:

SELECT "NestID",
"FailCode",
sum (CASE WHEN "FailCode"=23 THEN 1 END) as "CH1", 
sum (CASE WHEN "FailCode"=11 THEN 1 END) as "CH2"
FROM public.vb10_cartridgedata WHERE "FailCode" != 0 
group by 1, 2;

Answer 2

Thanks a lot for your help. This is what I ended up using:

SELECT distinct ("NestID"), count (*) FILTER (WHERE "FailCode" =23) as "CH1", count (*) FILTER (WHERE "FailCode" =11) as "CH2" FROM public.vb10_cartridgedata group by 1 ;