Sql Server How to Return Null Instead of 0 If a Grouped Value Doesn't Have Rows in Source

SQL Server How To Return NULL Instead of 0 IF A GROUPED Value Doesn't Have Rows in Source

You simple need to change NULL to zero before the aggregation.

Standard SQL solution, runnning on every DBMS:

SELECT
   Contestant,
   SUM ( CASE WHEN GameRound = 1 THEN COALESCE(RoundWinningsAmount,0) END) AS Round_1_Winnings,
   SUM ( CASE WHEN GameRound = 2 THEN COALESCE(RoundWinningsAmount,0) END) AS Round_2_Winnings,
   SUM ( CASE WHEN GameRound = 3 THEN COALESCE(RoundWinningsAmount,0) END) AS Round_3_Winnings
FROM dbo.GameWinnings
GROUP BY Contestant;

Return a value if no rows are found in Microsoft tSQL

SELECT CASE WHEN COUNT(1) > 0 THEN 1 ELSE 0 END AS [Value]

FROM Sites S

WHERE S.Id = @SiteId and S.Status = 1 AND 
      (S.WebUserId = @WebUserId OR S.AllowUploads = 1)

Count Returning blank instead of 0

You cannot expect any records to be outputted when using a GROUP BY clause, when no records exist in your source.

If you want an output of 0 from the SUM and COUNT functions, then you should not use GROUP BY.

The reason is that when you have no records, the GROUP BY clause has nothing to group by, and then is not able to give you any output.

For example:

SELECT COUNT(*) FROM (SELECT 'Dummy' AS [Dummy] WHERE 1 = 0) DummyTable

will return one record with the value '0', where as:

SELECT COUNT(*) FROM (SELECT 'Dummy' AS [Dummy] WHERE 1 = 0) DummyTable
GROUP BY [Dummy]

will return no records.

SQL Server group by absorb null and empty values

You can't have a field in the SELECT statement unless it's part of the GROUP BY clause or used for aggregation. The question and desired output shows that the rows should be grouped by name, which means all other fields (ID,amount, comments) should be aggregated.

The question doesn't specify how the IDs should be aggregated, or which comments should appear. Aggregating strings is only possible using functions like MIN/MAX in all SQL Server versions up to 2016. SQL Server 2017 added STRING_AGG to concatenate strings. In earlier versions people have to use one of many string aggregation techniques that may involve XML or SQLCLR functions.

In SQL Server versions the desired output can be produced by

SELECT MIN(ID) as ID,name,sum(amount) as Amount, max(comment) as comments
from #table1 
group by name

This produces the desired output :

ID  name    Amount  comments
1   n1      421762  Hello
2   n2      5810    Bye

This assumes that there is only one non-empty comment. The question doesn't specify something different though.

In SQL Server 2017 multiple comments can be concatenated with STRING_AGG :

SELECT MIN(ID) as ID,name,sum(amount) as Amount, STRING_AGG(comment,' ') as comments
from table1 
group by name

Given the question's data, this will also produce the desired output.

ID  name    Amount  comments
1   n1      421762  Hello
2   n2      5810    Bye

Include null/0 rows in a group by

This should do:

SELECT  u.NoEmploye, u.FirstName, u.LastName, Total=sum(h.NbHeures), ac.Name
FROM ActivityCategory ac
LEFT JOIN ActivityCodes code
    ON code.Categorie=ac.Id
LEFT JOIN Heures h
    ON h.Code=code.ActivityId
LEFT JOIN HeuresProjets hp
    ON hp.HPId=h.HpGuid
LEFT JOIN Semaines s
    ON s.Id=hp.WeekId
LEFT JOIN Users u
    ON u.EntityGuid=s.UserGuid
GROUP BY u.NoEmploye, u.FirstName, u.LastName, ac.Name
ORDER BY u.NoEmploye

Basically, if you want all Categories, you need to use that table as the first table on your FROM and do LEFT JOINs to that table.

UPDATE
If you want every category for every user on your results, you'll need a CROSS JOIN:

SELECT  u.NoEmploye, u.FirstName, u.LastName, Total=sum(h.NbHeures), ac.Name
FROM ActivityCategory ac
CROSS JOIN Users u
LEFT JOIN ActivityCodes code
    ON code.Categorie=ac.Id
LEFT JOIN Heures h
    ON h.Code=code.ActivityId
LEFT JOIN HeuresProjets hp
    ON hp.HPId=h.HpGuid
LEFT JOIN Semaines s
    ON s.Id=hp.WeekId AND u.EntityGuid=s.UserGuid
GROUP BY u.NoEmploye, u.FirstName, u.LastName, ac.Name
ORDER BY u.NoEmploye

To solve the issue when you want to add a WHERE clause:

SELECT  u.NoEmploye, u.FirstName, u.LastName, 
        Total=COALESCE(SUM(h.NbHeures),0), ac.Name
FROM ActivityCategory ac
CROSS JOIN Users u
LEFT JOIN 
        ActivityCodes code
        JOIN Heures h
            ON h.Code=code.ActivityId
        JOIN HeuresProjets hp
            ON hp.HPId=h.HpGuid
        JOIN Semaines s
            ON s.Id=hp.WeekId 
            AND (some condition on the dates)            -- add here
    ON  ac.Id = code.Categorie
    AND u.EntityGuid = s.UserGuid
GROUP BY u.NoEmploye, u.FirstName, u.LastName, ac.Name
ORDER BY u.NoEmploye

How can I include null values in a MIN or MAX?

It's a bit ugly but because the NULLs have a special meaning to you, this is the cleanest way I can think to do it:

SELECT recordid, MIN(startdate),
   CASE WHEN MAX(CASE WHEN enddate IS NULL THEN 1 ELSE 0 END) = 0
        THEN MAX(enddate)
   END
FROM tmp GROUP BY recordid

That is, if any row has a NULL, we want to force that to be the answer. Only if no rows contain a NULL should we return the MIN (or MAX).

---

Related Topics