多个 Sql 计数

Question

我的数据库中有一个表（tbl_MatterItem），用于跟踪项目发送和接收的日期。如果尚未发送或接收，则值为 NULL。我有另一个要链接的表 (tbl_Matter)，以获取该特定项目的 BillingLawyer。

BillingLawyer     ItemSent     ItemReceived
-------------     --------     ------------
   Alison         09/09/09        NULL
   Alison         10/10/10        NULL
   Alison         11/11/11       13/11/11
   Alison         12/12/12        NULL

我想为每位律师检索一行，其中包含他们的姓名、发送的项目数量和收到的项目数量。

BillingLawyer    Sent    Received
-------------    -----   --------
   Alison         4         1

以下是我到目前为止所得到的：

SELECT BillingLawyer, 
(SELECT COUNT(DISTINCT itemSent)FROM tbl_matteritem mit WHERE itemid=2 AND itemSent IS NOT NULL AND mit.ItemSent= mi.itemSent) AS [Sent], 
(SELECT COUNT(DISTINCT itemReceived)FROM tbl_matteritem mitm WHERE itemid=2 AND itemReceived IS NOT NULL AND mitm.itemreceived = mi.itemreceived)AS Received
FROM tbl_matteritem mi JOIN tbl_matter m ON mi.matterid = m.matterid
GROUP BY BillingLawyer, ItemSent, itemreceived
ORDER BY 1

但是我的结果显示了额外的记录以及似乎是位值的内容？：

BillingLawyer    Sent    Received
-------------    ----    --------
   Alison         0         0
   Alison         1         1
   Alison         1         0
   Alison         1         0
   Alison         1         0

我有什么想法出错了吗？

Answer 1

试试这个：

SELECT
    BillingLawyer,
    SUM(CASE WHEN ItemSent IS NOT NULL THEN 1 ELSE 0 END) AS Sent,
    SUM(CASE WHEN ItemReceived IS NOT NULL THEN 1 ELSE 0 END) AS Received
FROM
    tbl_matteritem mi
JOIN tbl_matter m ON
    mi.matterid = m.matterid
GROUP BY BillingLawyer
ORDER BY BillingLawyer

Answer 2

您正在对 ItemSent 进行分组，因此 count(distinct ItemSent) 将返回 1 或 0。毕竟，将为每个不同的 ItemSent，然后 count() 将应用于该组。

如果您想对每个 BillingLawyer 的项目进行计数，请对 BillingLawyer 进行分组，而不是对 ItemSent 进行分组。

Answer 3

我认为首先删除 DISTINCT 因为它会计算每个日期。

SELECT BillingLawyer, 
(SELECT COUNT( itemSent)FROM tbl_matteritem mit WHERE itemid=2 AND itemSent IS      NOT NULL AND mit.ItemSent= mi.itemSent) AS [Sent], 
(SELECT COUNT( itemReceived)FROM tbl_matteritem mitm WHERE itemid=2 AND itemReceived IS NOT NULL AND mitm.itemreceived = mi.itemreceived)AS Received
FROM tbl_matteritem mi JOIN tbl_matter m ON mi.matterid = m.matterid
GROUP BY BillingLawyer, ItemSent, itemreceived
ORDER BY 1

Answer 4

请勿在 GROUP BY 中包含 ItemSent, itemreceived。
仅使用GROUP BY BillingLawyer