How to Include Results of SQL Count If Count=0

How to include "zero" / "0" results in COUNT aggregate?

You want an outer join for this (and you need to use person as the "driving" table)

SELECT person.person_id, COUNT(appointment.person_id) AS "number_of_appointments"
FROM person 
  LEFT JOIN appointment ON person.person_id = appointment.person_id
GROUP BY person.person_id;

The reason why this is working, is that the outer (left) join will return NULL for those persons that do not have an appointment. The aggregate function count() will not count NULL values and thus you'll not get a zero.

If you want to learn more about outer joins, here is a nice tutorial: http://sqlzoo.net/wiki/Using_Null

How to include results of SQL count if count=0?

You would use a left join. The trick is to get the count() and date conditions right. I would write this as:

SELECT l.noten, COUNT(d.noten) as count
FROM lookup l LEFT JOIN
     data d
     ON d.noten = l.noten AND
        d.date >= '2018-10-23' AND d.date < '2018-10-24'
GROUP BY l.noten
ORDER BY l.noten;

In particular, notice that the date logic doesn't need time components to accomplish what you want.

Include 0 in count(*) SQL query

It's not every day I get to write a CROSS JOIN:

SELECT u.ID, s.status, 
    coalesce((SELECT COUNT(*) FROM ma_base m WHERE m.User_Id = u.ID and m.status = s.Status),0) As Status_Count
FROM User u
CROSS JOIN (SELECT DISTINCT status FROM MA_Base) s
WHERE u.ID = 5

OR:

SELECT u.ID, s.status, COALESCE(COUNT(m.status), 0) AS Status_Count
FROM User u
CROSS JOIN (SELECT DISTINCT status FROM MA_Base) s
LEFT JOIN MA_Base m ON m.User_Id = u.ID AND m.status = s.status
WHERE u.ID = 5
GROUP BY u.ID, s.status

In a nutshell, we first need to create a projection for the user with every possible status value, to anchor the result records for your "missing" statuses. Then we can JOIN or do a correlated subquery to get your desired results.

For the JOIN option, note the expression in the COUNT() function. It's important; COUNT(*) won't do what you want. For both options, note the use of COALESCE() to put the expected result in for NULL.

If you have a separate table defining your status values, use that instead of deriving them from ma_base.

SQL COUNT returns No Data when the result should be 0

There's a few things to change from your very close attempt:

1. Move your where condition for barcelona into the ON for your join of that table instead. Otherwise your LEFT OUTER JOIN becomes an implicit INNER JOIN
2. Also instead of Count(*), just grab the count for b.host where a NULL for that column won't figure into the count.
3. Lastly swap the order in which you are joining these tables. You want all values from TableA and only those from TableB that meet your criteria.

---

SELECT A.timestamp, Count(b.host)
FROM tableA as A
LEFT JOIN tableB as B ON A.host = B.host AND B.location= 'Barcelona'
WHERE A.current_state != 0 
GROUP BY A.timestamp

Return zero if no results using COUNT-GROUP BY

Assuming you have some data in the table for the months you want, you can switch to conditional aggregation:

select datename(month, date_occu) as MonthName, 
       datepart(month, date_occu) as MonthNumber,
       sum(case when agency = 'WCSO' and offense = 'DEATH INVESTIGATION' then 1 else 0 end) as Quantity
from crimes.rms4gis.dbo.tmprms4gisall 
where datediff(month, date_occu, getdate()) between 1 and 3 
group by datename(month, date_occu), datepart(month, date_occu)
order by MonthNumber;

There are other methods that involve generating the three months involved. However, that requires a somewhat more complex query. The above is a simple solution that should work in this -- and many other -- situations.

How to include zero-count results in query

Because you have a reference to Calls.CallDate in your HAVING clause, you are removing operators where there are no calls. If there were no calls, then CallDate would be NULL, and NULL=20170104 is not true, so these rows are excluded. You need to move this predicate to your join clause:

SELECT Operators.id, Operators.Nome, Count(Calls.OpId) AS CountCalls
FROM Operators LEFT JOIN Calls ON (Operators.id = Calls.OpId AND Calls.CallDate=20170104)
GROUP BY Calls.CallDate, Operators.id, Operators.Nome;

You also don't need to group by Calls.CallDate, since you only have one anyway, so you can just use:

SELECT Operators.id, Operators.Nome, Count(Calls.OpId) AS CountCalls
FROM Operators LEFT JOIN Calls ON (Operators.id = Calls.OpId AND Calls.CallDate=20170104)
GROUP BY Operators.id, Operators.Nome;

As an aside HAVING is the wrong operator. HAVING is for filtering aggregates, since you are not filtering an aggregate, you should simply use WHERE

SELECT Operators.id, Operators.Nome, Count(Calls.OpId) AS CountCalls
FROM Operators LEFT JOIN Calls ON Operators.id = Calls.OpId
WHERE Calls.CallDate=20170104
GROUP BY Calls.CallDate, Operators.id, Operators.Nome;

You would use HAVING if you wanted to fliter on CountCalls, e.g if you only wanted operators that had made more than 1 call you might use:

SELECT Operators.id, Operators.Nome, Count(Calls.OpId) AS CountCalls
FROM Operators LEFT JOIN Calls ON Operators.id = Calls.OpId
WHERE Calls.CallDate=20170104
GROUP BY Calls.CallDate, Operators.id, Operators.Nome
HAVING Count(Calls.OpId) > 1;

This would only return

Id Nome CountCalls
--+----+----------
 1 JDOE          2

Display count 0 values in SQL Server

One approach would be to use conditional aggregation:

Select Col1, Col2, Count(case when Col3 = 1 then 1 end) as CountOfRecords
From TableA
Group By Col1, Col2

---

Related Topics