Return null values in GROUP BY query-CodePudding

This only returns results for entries that exist, which makes sense, but I'm trying to get it to display all of the B.[HostName] entries, even if there aren't entries for all of them. I'd like it to show 0 under the count for those. I've read about needing to use a LEFT JOIN on some of the tables and changing the COUNT(*) to use a field instead, but whenever I have tried that, it still results in the same data. Can anyone show me what would need to be changed for this to work the way I mentioned?

EDIT: To clarify, since the initial answers didn't work: The count would be coming from this table: [Database].[dbo].[WorkItemHistory] A -- we want all of the entries of A.[PlatformId]

A.[PlatformId] is a foreign key to C.[Id] AND C.[EngineId] is a foreign key to B.[Id]

From there, we are getting B.[HostName]

So, all entries for B.[HostName] would be listed in the output and the count would come from the entries in the A.[WorkitemHistory] table.

We are using

SELECT DISTINCT B.[HostName], COUNT(*) AS Count
FROM [Database].[dbo].[WorkItemHistory] A, [Database].[dbo].[Engine] B, [Database].[dbo].[Platforms] C 
WHERE A.[PlatformId] = C.[Id]
AND B.[Id] = C.[EngineId]
AND A.[Status] = '30'
AND A.[LastAttemptDateTime] >= CAST(GETDATE() AS Date)
GROUP BY B.[HostName]
ORDER BY COUNT(*) DESC

CodePudding user response：

Replace your JOIN with a RIGHT JOIN B like below to give values of B that doesnt have any common values of B in others.

SELECT  B.[HostName], COUNT(*) AS Count
FROM [Database].[dbo].[WorkItemHistory] A 
JOIN [Database].[dbo].[Platforms] C 
On A.[PlatformId] = C.[Id]
RIGHT JOIN [Database].[dbo].[Engine] B
On C.[id]=B.[EngineId]
Where A.[Status] = '30'
AND A.[LastAttemptDateTime] >= CAST(GETDATE() AS Date)
GROUP BY B.[HostName]
ORDER BY COUNT(*) DESC

CodePudding user response：

I think Something like this will do it. You just need to left join the other tables to [Database].[dbo].[Engine] since you want all the hostnames.

SELECT B.[HostName], 
       COUNT(C.ID) AS Count
FROM [Database].[dbo].[Engine] B
LEFT JOIN [Database].[dbo].[Platforms] C
  ON B.[Id] = C.[EngineId]
LEFT JOIN [Database].[dbo].[WorkItemHistory] A  
  ON A.[PlatformId] = C.[Id]
     AND A.[Status] = '30'
     AND AND A.[LastAttemptDateTime] >= CAST(GETDATE() AS Date)
GROUP BY B.[HostName]     
ORDER BY COUNT(*) DESC

CodePudding user response：

If you put some of the criteria of a left joined table in the where clause, then it'll behave like an inner join.

SELECT E.HostName, COUNT(*) AS Count
FROM [Database].[dbo].[Engine] AS E
LEFT JOIN [Database].[dbo].[Platforms] AS P 
  ON P.EngineId = E.Id
LEFT JOIN [Database].[dbo].[WorkItemHistory] AS H
  ON H.PlatformId = P.Id
 AND H.Status = '30'
 AND H.LastAttemptDateTime >= CAST(GETDATE() AS Date)
GROUP BY E.HostName
ORDER BY COUNT(*) DESC