set default value on query column-CodePudding

I have a query with columns "Cremembers.Name" (this pulls the "Name" column from the table "Crewmembers") and "Sum of HoursUW" (this sums "HoursUW" from a different table "Marine391" per "Crewmember.Name". There is an existing relationship between Cremembers.Name and Marine391.Crewmembers where all Cremembers.Name values are listed and only values from Marine391.Crewmembers are listed where the joined fields are equal.

In this query, if a crewmember does not have any HoursUW, I want the default value of "Sum of HoursUW" to be 0.

This is the current SQL code:

SELECT DISTINCTROW Crewmembers.Name, Sum(Marine391_29ft_SAFEBOAT.HoursUW) AS [Sum Of HoursUW]
FROM Crewmembers LEFT JOIN Marine391_29ft_SAFEBOAT ON Crewmembers.[Name] = Marine391_29ft_SAFEBOAT.[Crewmembers].[Value]
GROUP BY Crewmembers.Name;

I tried using the nz() function like this:

nz((SELECT DISTINCTROW Crewmembers.Name, Sum(Marine391_29ft_SAFEBOAT.HoursUW), 0) AS [Sum Of HoursUW]
FROM Crewmembers LEFT JOIN Marine391_29ft_SAFEBOAT ON Crewmembers.[Name] = Marine391_29ft_SAFEBOAT.[Crewmembers].[Value]
GROUP BY Crewmembers.Name;

and like this:

SELECT DISTINCTROW Crewmembers.Name, nz(Sum(Marine391_29ft_SAFEBOAT.HoursUW), 0) AS [Sum Of HoursUW]
FROM Crewmembers LEFT JOIN Marine391_29ft_SAFEBOAT ON Crewmembers.[Name] = Marine391_29ft_SAFEBOAT.[Crewmembers].[Value]
GROUP BY Crewmembers.Name;

These both had syntax errors.

How can I use the nz() function or is there a better way to set the default value to 0 in this query?

CodePudding user response：

Apply Nz() directly to the HoursUW field as you Sum() it.

SELECT cm.Name, Sum(Nz(msb.HoursUW, 0)) AS [Sum Of HoursUW]
FROM
    Crewmembers AS cm
    LEFT JOIN Marine391_29ft_SAFEBOAT AS msb
    ON cm.[Name] = msb.[Crewmembers].[Value]
GROUP BY cm.Name;

I did not include DISTINCTROW because the query's result set rows will already be distinct by virtue of the GROUP By.