I have the following tables:
items_type_catalog
id | description |
---|---|
1 | itc1 |
2 | itc2 |
3 | itc3 |
4 | itc4 |
items_header
id | stato | tipo | progetto |
---|---|---|---|
1 | 4 | 1 | 1 |
2 | 2 | 3 | 1 |
3 | 4 | 3 | 1 |
4 | 4 | 1 | 1 |
5 | 2 | 1 | 1 |
6 | 4 | 1 | 1 |
7 | 4 | 1 | 1 |
8 | 1 | 1 | 1 |
If I run this query I get all types from the first table and null for those that don’t have any data:
<code>SELECT items_type_catalog.id,
items_type_catalog.description,
items_header.tipo,
COUNT(CASE WHEN (items_header.stato = 4 OR items_header.stato= 5) THEN 1 END) AS solved,
count(items_header.id) as tutti
FROM items_type_catalog LEFT JOIN items_header ON items_type_catalog.id=items_header.tipo
GROUP by items_type_catalog.id, tipo;
</code>
<code>SELECT items_type_catalog.id,
items_type_catalog.description,
items_header.tipo,
COUNT(CASE WHEN (items_header.stato = 4 OR items_header.stato= 5) THEN 1 END) AS solved,
count(items_header.id) as tutti
FROM items_type_catalog LEFT JOIN items_header ON items_type_catalog.id=items_header.tipo
GROUP by items_type_catalog.id, tipo;
</code>
SELECT items_type_catalog.id,
items_type_catalog.description,
items_header.tipo,
COUNT(CASE WHEN (items_header.stato = 4 OR items_header.stato= 5) THEN 1 END) AS solved,
count(items_header.id) as tutti
FROM items_type_catalog LEFT JOIN items_header ON items_type_catalog.id=items_header.tipo
GROUP by items_type_catalog.id, tipo;
Result:
id | description | tipo | solved | tutti |
---|---|---|---|---|
1 | itc1 | 1 | 4 | 6 |
2 | itc2 | NULL | 0 | 0 |
3 | itc3 | 3 | 1 | 2 |
4 | itc4 | NULL | 0 | 0 |
but I need to filter by progetto (in the sample data it is always 1 but can change). So if I do:
<code>SELECT items_type_catalog.id,
items_type_catalog.description,
items_header.tipo,
COUNT(CASE WHEN (items_header.stato = 4 OR items_header.stato= 5) THEN 1 END) AS solved,
count(items_header.id) as tutti
FROM items_type_catalog LEFT JOIN items_header ON items_type_catalog.id=items_header.tipo
WHERE progetto=1
GROUP by items_type_catalog.id, tipo;
</code>
<code>SELECT items_type_catalog.id,
items_type_catalog.description,
items_header.tipo,
COUNT(CASE WHEN (items_header.stato = 4 OR items_header.stato= 5) THEN 1 END) AS solved,
count(items_header.id) as tutti
FROM items_type_catalog LEFT JOIN items_header ON items_type_catalog.id=items_header.tipo
WHERE progetto=1
GROUP by items_type_catalog.id, tipo;
</code>
SELECT items_type_catalog.id,
items_type_catalog.description,
items_header.tipo,
COUNT(CASE WHEN (items_header.stato = 4 OR items_header.stato= 5) THEN 1 END) AS solved,
count(items_header.id) as tutti
FROM items_type_catalog LEFT JOIN items_header ON items_type_catalog.id=items_header.tipo
WHERE progetto=1
GROUP by items_type_catalog.id, tipo;
result is:
id | description | tipo | solved | tutti |
---|---|---|---|---|
1 | itc1 | 1 | 4 | 6 |
3 | itc3 | 3 | 1 | 2 |
so I loose the NULL values that I expected to be there anyway
What am I doing wrong?