SQL count show 0 if no rows including where condition

Question

I'm doing a COUNT but cannot get the value 0 when there are no rows in the result.

If I remove the where condition:

AND documentstats.OPENINGDATE >= '2021-01-01T00: 00: 00.000'

it works fine and I get the value 0 when there are no rows in the result.

I am looking for an option to return value 0 in the NumberOfViews column when no rows are found in my count.

Can anyone help me?

SELECT 
    customertodocument.DocId,
    COUNT (documentstats.DocId) AS NumberOfViews
FROM 
    customertodocument 
LEFT JOIN 
    documentstats ON customertodocument.DocId = documentstats.DocId 
                  AND customertodocument.customerId = documentstats.customerId
WHERE 
    customertodocument.customerId = '1111'
    AND documentstats.openingdate >= '2021-01-01T00:00:00.000'
GROUP BY 
    customertodocument.DocId
ORDER BY 
    NumberOfViews ASC

Move the documentstats.openingdate condition from WHERE to ON to get true LEFT JOIN result. — jarlh, Jan 15 '21 at 07:09
Thanks for your help everyone. The solution was to Move the documentstats.openingdate condition from WHERE to ON to get true LEFT JOIN result. Actually a much better descriped case om same subjekt I Foundation here https://stackoverflow.com/questions/219046/help-with-a-where-on-a-left-join-sql-query — Andreas Nielsen, Jan 15 '21 at 21:37

score 2 · Answer 1 · answered Jan 15 '21 at 12:36

The second condition in the WHERE clause is filtering out all non-matches. Because you have an explicit GROUP BY, the query will return no rows if the FROM clause has no rows.

If you want counts of 0, then move the condition to the ON clause of the LEFT JOIN. Note: Conditions on the second table go in the ON clause.

The query should look like:

SELECT cd.DocId, COUNT(ds.DocId) AS NumberOfViews
FROM customertodocument cd LEFT JOIN
     documentstats ds
     ON cd.DocId = ds.DocId AND
        cd.customerId = ds.customerId AND
        ds.openingdate >= '2021-01-01'
WHERE ds.customerId = 1111
GROUP BY cd.DocId
ORDER BY NumberOfViews ASC;

Notes:

Table aliases make the query easier to write and to read.
customerId looks like a number. If it is, then the comparison should be to a number. If the id is really a string, put the single quotes back in.
You have a date constant. There is no need to include the time. No real harm, except it clutters the query.

score 0 · Answer 2 · answered Jan 15 '21 at 06:59

My guess is that there are no records in the data that meet both of the conditions. You are grouping by customertodocument.DocId, but if no values of customertodocument.DocID exist after filtering with the WHERE clause, the aggregation will have nothing to group by and you'll get no results. You can test this by running the following query:

SELECT *
FROM customertodocument
LEFT JOIN documentstats on customertodocument.DocId = documentstats.DocId and customertodocument.customerId = documentstats.customerId
WHERE customertodocument.customerId = '1111' AND documentstats.openingdate >= '2021-01-01T00:00:00.000'

Thanks for your reply. I get the correct result when I do the sql query so I know that there are several records in the data that meet both of the conditions. The only thing I miss is the option to return value 0 in the NumberOfViews column when no rows are found. — Andreas Nielsen, Jan 15 '21 at 08:07

score 0 · Answer 3 · answered Jan 15 '21 at 07:02

0

your WHERE condition returns nothing so you are not getting any record. Count alone in select clause can give you 0 but you have one column and then count so you are not getting any record

What value you are expecting in the customertodocument.DocId if no matching record found.

You can get the 0 count if you remove the customertodocument.DocId from select clause keeping only count in select clause and removing the GROUP BY clause

answered Jan 15 '21 at 07:02

Popeye

35,427
4
10
31

Thanks for your reply. I cannot do without customertodocument.DocId because I need to see all posts in customertodocument and how many times DocId appears in documentstats. In the customertodocument.DocId I would expect the docID from the customertodocument table. If there are no results of the DocId in the documentstats table I would expect 0 in the NumberOfViews column. – Andreas Nielsen Jan 15 '21 at 07:47

SQL count show 0 if no rows including where condition

3 Answers3