I need to select a data that doesn't exist on a table as zero using the COUNT() function, how do I do so using Sql

Question

For instance:
SELECT count(num) AS number, Status
FROM base
WHERE Status in ('P', 'J', 'K')
group by Status;

Note: J and K doesn't exist in the Status column but I need to display the number as zero

Answer 1

So your query at 'where' conditions its useless, because there are no J and K in the Status column.

What i get from what you said, you need to show status J and K with 0 value at column, if that right so you can use UNION.

//
SELECT count(num) AS number, status FROM base WHERE status ='P'
group by status

UNION ALL
SELECT 0 AS number, 'J' AS status

UNION ALL
SELECT 0 AS number, 'K' AS status
//

The query results will be like this :
number || status
5 || P
0 || J
0 || K