CODEWITHSUNDEEP
sqlgroup-bycountnull
Micky
Micky

Reputation: 47

How to count 0 when row return null

I have created table from which I want to count the number work based on work status.

This my table

This is the query I have used:

SELECT Workid, COUNT(*)
FROM WORK WHERE STATUS = 'awarded'
GROUP BY Workid

Output I am getting:

Output I am getting

Output I want:

Output I want

Upvotes: 1

Views: 85

Answers (2)

forpas
forpas

Reputation: 164099

The WHERE clause in your query:

WHERE STATUS = 'awarded'

filters out all these rows that you want to include in the results with 0 in the column Count.

So, you have to remove it and include the condition STATUS = 'awarded' inside the COUNT() aggregate function with a CASE expression:

SELECT Workid, 
       COUNT(CASE WHEN STATUS = 'awarded' THEN 1 END) AS Count
FROM WORK 
GROUP BY Workid;

This way the query aggregates on all rows of the table and will return 0 when the condition STATUS = 'awarded'is not satisfied for a specific Workid.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

One method is conditional aggregation:

SELECT Workid, SUM(CASE WHEN STATUS = 'awarded' THEN 1 ELSE 0 END)
FROM WORK 
GROUP BY Workid;

This assumes that all WorkIds have at least one row in the table, regardless of status.

Upvotes: 1

Related Questions