FInd rows with 0 count from group by sql

Question

I have a select query to select an ID and corresponding count and group them based on ID and this is the sql for that

SELECT InID, COUNT(*) as ICount FROM RawData WHERE CompletedDate>= @BeginDate AND CompletedDate<= @EndDate AND InID in (3851,4151,11) GROUP BY InID

This returns only one record for me

4151 225

To find the missing entries ie 3851 and 11 , i tried the query

SELECT InID, COUNT(*) as ICount FROM RawData WHERE CompletedDate>= @BeginDate AND CompletedDate<= @EndDate AND InID in (3851,4151,11) GROUP BY InID HAVING COUNT(*)=0

But it returned 0 records. So to check the IDs with missing records in a group by what is the proper way

Create a calendar table. Do an outer join.

jarlh
– jarlh

2018-01-10 09:53:21 +00:00
Commented Jan 10, 2018 at 9:53 — jarlh
– jarlh, Commented Jan 10, 2018 at 9:53

Jayasurya Satheesh · Accepted Answer · 2018-01-10 10:24:11Z

This won't return you a row if there are no records for the specified Id's That satisfy the condition in the Where Clause. You Can try this Logic

;WITH CTE AS ( SELECT MyId = 3851 UNION ALL SELECT 4151 UNION ALL SELECT 11 ) SELECT CTE.MyId, COUNT(1) FROM CTE LEFT JOIN RawData RD ON CTE.MyId = RD.InID WHERE CompletedDate>= @BeginDate AND CompletedDate<= @EndDate GROUP BY CTE.MyId

Or Simply this will also work for you

select InID, SUM(CASE WHEN CompletedDate>= @BeginDate AND CompletedDate<= @EndDate THEN 1 ELSE 0 END) as ICount FROM RawData whereInID in (3851,4151,11) group by InID

To Filter records with Zero count

select InID, SUM(CASE WHEN CompletedDate>= @BeginDate AND CompletedDate<= @EndDate THEN 1 ELSE 0 END) as ICount FROM RawData whereInID in (3851,4151,11) group by InID having SUM(CASE WHEN CompletedDate>= @BeginDate AND CompletedDate<= @EndDate THEN 1 ELSE 0 END) = 0

Like the second approach , Can we make it to list rows with 0 count only?

Shushil Bohara · Accepted Answer · 2018-01-10 10:06:45Z

TRY THIS: I think you can achieve your desired output through OUTER APPLY as below:

SELECT InID, rd1.ICount FROM RawData rd OUTER APPLY (SELECT COUNT(*) ICount FROM RowDate rd1 WHERE rd1.InID = rd.InID AND rd1.CompletedDate>= @BeginDate AND rd1.CompletedDate<= @EndDate) rd1 WHERE InID IN (3851,4151,11) GROUP BY InID

IDs those doesn't have records will display NULL.

Denis Rubashkin · Accepted Answer · 2018-01-10 10:08:09Z

Using LEFT JOIN:

SELECT r.InID, ISNULL(c.ICount, 0) as ICount FROM RawData r LEFT JOIN ( select InID, COUNT(*) as ICount FROM RawData where CompletedDate>= @BeginDate AND CompletedDate<= @EndDate and InID in (3851,4151,11) group by InID ) c ON c.InID = r.InID WHERE r.InID in (3851,4151,11)

Using OUTER APPLY:

SELECT r.InID, ISNULL(c.ICount, 0) as ICount FROM RawData r OUTER APPLY ( select COUNT(*) as ICount FROM RawData where CompletedDate>= @BeginDate AND CompletedDate<= @EndDate and InID = r.InID ) c WHERE r.InID in (3851,4151,11)

podiluska · Accepted Answer · 2018-01-10 10:06:42Z

You need to do a left join from a resultset that contains all the values you want to include

select ids.Id, count(InId) from (select 3851 as Id union select 4151 union select 11) ids left join RawData on ids.id = rawdata.inid where CompletedDate>= @BeginDate AND CompletedDate<= @EndDate group by Ids.id

Collectives™ on Stack Overflow

FInd rows with 0 count from group by sql

4 Answers 4

1 Comment

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Related