Returning record with SUM value of 0 (zero) for empty recordset.

[f***@gmail.com]

SELECT Subgroups.SubgroupID, DateTime, PtVar, SpecialFlags, SUM(DataValues.Value) AS [Value]
From Subgroups, DataValues
WHERE Subgroups.SubgroupID >= 4343
AND Subgroups.CharID = 309
AND Subgroups.SubgroupID = DataValues.SubgroupID
AND DataValues.SampleNumber IN (SELECT DefectID FROM DefectDescriptions WHERE Type = 95 )
GROUP BY Subgroups.SubgroupID, DateTime, PtVar, SpecialFlags

DataValues has 3 columns:
DataValues(SubgroupID, SampleNumber, Value)

No record is entered for a Value of zero to save space since 90% will be zero.

However, I need the query to return a record for every Subgroups record with and have "SUM(DataValues.Value) = 0" for those records, even though no matching records are found to SUM.

[rpresser]

Post by f***@gmail.com
However, I need the query to return a record for every Subgroups
record with and have "SUM(DataValues.Value) = 0" for those records,
even though no matching records are found to SUM.

You need a LEFT JOIN.

SELECT Subgroups.SubgroupID, DateTime, PtVar, SpecialFlags,
SUM(DataValues.Value) AS [Value]
FROM Subgroups
LEFT JOIN DataValues ON Subgroups.SubgroupID=DataValues.SubgroupID
WHERE Subgroups.SubgroupID >= 4343
AND Subgroups.CharID = 309
AND DataValues.SampleNumber IN
(SELECT DefectID FROM DefectDescriptions WHERE Type=95)
GROUP BY Subgroups.SubgroupID, DateTime, PtVar, SpecialFlags

[f***@gmail.com]

Using your query returns the same records as mine.

It doesn't include the Subgroups records that don't have matching records in the DataValues table.

[f***@gmail.com]

I tried 'RIGHT JOIN' also.

'FULL OUTER JOIN' gives a syntax error in the FROM clause.

[f***@gmail.com]

SELECT DISTINCT * FROM
(
SELECT SubgroupID, DateTime, PtVar, SpecialFlags, 0
FROM Subgroups
WHERE Subgroups.SubgroupID >= 4343
AND Subgroups.CharID = 309
AND Subgroups.Deleted = 0

UNION SELECT Subgroups.SubgroupID, DateTime, 0, SpecialFlags, SUM(DataValues.Value) AS [Value]
From Subgroups, DataValues
WHERE Subgroups.SubgroupID >= 4343
AND Subgroups.CharID = 309
AND Subgroups.Deleted = 0
AND Subgroups.SubgroupID = DataValues.SubgroupID
AND DataValues.SampleNumber IN (SELECT DefectID FROM DefectDescriptions WHERE Type = 95 )
GROUP BY Subgroups.SubgroupID, DateTime, PtVar, SpecialFlags
)
ORDER BY SubgroupID

This works, except it gives 2 records for those that have matching datavalues records. I can identify these and by replacing PtVar with 0 and thus will only be counted once.

[Erland Sommarskog]

Post by rpresser
You need a LEFT JOIN.
SELECT Subgroups.SubgroupID, DateTime, PtVar, SpecialFlags,
SUM(DataValues.Value) AS [Value]
FROM Subgroups
LEFT JOIN DataValues ON Subgroups.SubgroupID=DataValues.SubgroupID
WHERE Subgroups.SubgroupID >= 4343
AND Subgroups.CharID = 309
AND DataValues.SampleNumber IN
(SELECT DefectID FROM DefectDescriptions WHERE Type=95)
GROUP BY Subgroups.SubgroupID, DateTime, PtVar, SpecialFlags

But with a condition on DataValues in the WHERE clause, this is still
an inner join. Furthermore, the SUM will yield NULL, not zero for the
missing groups.

Whence:

SELECT S.SubgroupID, DateTime, PtVar, SpecialFlags,
isnull(SUM(DV.Value), 0) AS [Value]
FROM Subgroups S
LEFT JOIN DataValues DV
ON S.SubgroupID = DV.SubgroupID
AND DV.SampleNumber IN (SELECT DD.DefectID
FROM DefectDescriptions DD
WHERE DD.Type = 95)
WHERE S.SubgroupID >= 4343
AND S.CharID = 309
GROUP BY S.SubgroupID, DateTime, PtVar, SpecialFlags

I've also introduced aliases to enhance readbility.

[rpresser]

Post by Erland Sommarskog

Post by rpresser
You need a LEFT JOIN.

But with a condition on DataValues in the WHERE clause, this is still
an inner join. Furthermore, the SUM will yield NULL, not zero for the
missing groups.

(facepalm)

Sorry about that.