CODEWITHSUNDEEP
sqlcount
Andreas Nielsen
Andreas Nielsen

Reputation: 31

SQL count show 0 if no rows including where condition

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

Upvotes: 2

Views: 13313

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269883

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.

Upvotes: 6

Popeye
Popeye

Reputation: 35910

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

Upvotes: 0

Stu
Stu

Reputation: 41

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'

Upvotes: 0

Related Questions