通过sql从group中取0行计数

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

我有一个选择查询来选择一个ID和相应的计数，并根据ID对它们进行分组，这是sql

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

为了找到缺失的条目，即3851和11，我尝试了查询

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

但它返回了0条记录。因此，通过正确的方法检查组中缺少记录的ID

4 个解决方案

#1

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

如果没有指定Id的记录满足条件中的条件，则不会返回一行。你可以试试这个逻辑

;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

#2

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

试试这个：我认为您可以通过OUTER APPLY实现所需的输出，如下所示：

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.

那些没有记录的ID将显示NULL。

#3

Using LEFT JOIN:

使用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:

使用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)

#4

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

#1