CODEWITHSUNDEEP
sqlsql-servert-sql
Jose Constenla
Jose Constenla

Reputation: 121

How can this count return 0 if no row match?

I need the following query to return 0 if there is no match:

CREATE TABLE #Operations (
    Discriminator varchar(40)
);

INSERT INTO #Operations
VALUES
    ('HistoricoCarga'),
    ('HistoricoDescarga')
;

SELECT
    o.Discriminator,
    CASE 
        WHEN COUNT(*) IS NULL THEN 0
        ELSE COUNT(*)
    END AS Total
FROM 
    HistoricosOperacion ho
        RIGHT JOIN #Operations o
            ON ho.Discriminator = o.Discriminator
WHERE
    ho.FechaEnvioIntegracionUTC IS NULL
    AND DATEDIFF(MINUTE, ho.FechaUTC, GETUTCDATE()) > 10
GROUP BY o.Discriminator;

I've tried changing the count(*) to count(ho.Discriminator) or changing the temp table to:


CREATE TABLE #Operations (
    Discriminator varchar(40),
    dummy int
);

INSERT INTO #Operations
VALUES
    ('HistoricoCarga', 0),
    ...

I need the output to be as follows:

HistoricoCarga         0
HistoricoDescarga      0

Upvotes: 2

Views: 81

Answers (1)

Caius Jard
Caius Jard

Reputation: 74660

Don't COUNT(*), count the join column from the sparse side of the join

CREATE TABLE #Operations (
    Discriminator varchar(40)
);

INSERT INTO #Operations
VALUES
    ('HistoricoCarga'),
    ('HistoricoDescarga')
;

SELECT
    o.Discriminator,
    COUNT(ho.Discriminator) AS Total
FROM 
    
    #Operations o
    LEFT JOIN
    HistoricosOperacion ho
    ON ho.Discriminator = o.Discriminator AND DATEDIFF(MINUTE, ho.FechaUTC, GETUTCDATE()) > 10

GROUP BY o.Discriminator;

Moving the restriction out of the WHERE and into the ON means that only rows where both things are true will join. You could also consider :

#Operations o
LEFT JOIN
(SELECT * FROM HistoricosOperacion WHERE DATEDIFF(MINUTE, ho.FechaUTC, GETUTCDATE()) > 10) ho
ON ho.Discriminator = o.Discriminator

if it makes more sense to you

Upvotes: 2

Related Questions