How can this count return 0 if no row match?

user_7193362 0
Viewed 74

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
tsql sql-server sql
1 Answers

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

user_14106640
Related