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

8

Hi

Hope this helps

The "Where Clause" I used as a "Case Statement"

create test data

drop table #Temp
create table #Temp ( PressBrakeID varchar(10) , Dttm datetime)
insert into #Temp select 'PB1', '2024-12-17 00:47:40.997'
insert into #Temp select 'PB1', '2024-10-23 9:47:40.997'
insert into #Temp select 'PB1', '2024-09-05 2:47:40.997'

insert into #Temp select 'PB2', '2023-12-17 00:47:40.997'
insert into #Temp select 'PB2', '2023-10-23 9:47:40.997'
insert into #Temp select 'PB2', '2023-09-05 2:47:40.997'

insert into #Temp select 'PB3', '2020-12-17 00:47:40.997'
insert into #Temp select 'PB3', '2020-10-23 9:47:40.997'
insert into #Temp select 'PB3', '2020-09-05 2:47:40.997'

 WHERE year(dttm) = 2024

select 
     PressBrakeID 
	 ,sum(case when year(dttm) = 2024 then 1 else 0 end ) as cnt 
from 
   #Temp 
group by 
       PressBrakeID

image

9

Very slick, thank you for the response

Cheers
Andrew