Count(0 and group by

1

I wonder if someone could point me in the right direction please

I am running a count query with a group by

If I run my query for all records for each group i get

PB1 - 112
PB2 - 23
PB3 - 56
PB4 - 22

if I use a where clause for a specific date i get

PB2 - 23
PB4 - 22

Is there a way to return 0 for no records
ie

PB1 - 0
PB2 - 23
PB3 - 56
PB4 - 0

this is my current code

SELECT dbo.PBNames.PPN, COUNT(dbo.Pressbrakes.Pressbrake_ID) AS Bends FROM dbo.PBNames LEFT OUTER JOIN dbo.Pressbrakes ON dbo.PBNames.PPN = dbo.Pressbrakes.Pressbrake_ID WHERE (CAST(dbo.Pressbrakes.DateTime AS DATE) = CAST(GETDATE() AS DATE)) GROUP BY dbo.PBNames.PPN

Than you in anticipation
Andrew

2

I'm not sure and as I don't have any sample data i cannot test it myself but this is my first guess: remove the date from the WHERE and add it to the join:

SELECT
dbo.PBNames.PPN,
COUNT(dbo.Pressbrakes.Pressbrake_ID) AS Bends
FROM dbo.PBNames
LEFT OUTER JOIN dbo.Pressbrakes
ON dbo.PBNames.PPN = dbo.Pressbrakes.Pressbrake_ID AND (CAST(dbo.Pressbrakes.DateTime AS DATE) = CAST(GETDATE() AS DATE))
GROUP BY dbo.PBNames.PPN;

3

Will test it shortly thank you

4

You can use CASE Expression on the COUNT()

SELECT   n.PPN,  
         COUNT(CASE WHEN CAST(b.DateTime AS DATE) = CAST(GETDATE() AS DATE)
                    THEN b.Pressbrake_ID
                    END) AS Bends 
FROM     dbo.PBNames AS n
         LEFT OUTER JOIN dbo.Pressbrakes b ON n.PPN = b.Pressbrake_ID
GROUP BY n.PPN
5

Thank you so much for your help it worked like a dream

Regards
Andrew

6

Thank you for your help
Regards
Andrew

7

Thank you so much