SQL Server How to count/classify specific records in a one to many relationship?

dsf9zpds 于 2023-11-16 发布在其他

关注(0)|答案(1)|浏览(148)

Thanks so much for your continued assistance. I'm hoping to create a list of counties that my staff serve, and a column for the number of Misdemeanor Investigations, and another for the number of Felony Investigations. The Pending Charge and Court tables are outlined below. Court is the parent for Pending Charges and the CourtID Column of the Pending Charge table is = to the ID column of the Court table, i.e. JOIN vPendingCharges pc ON pc.CourtID = c.ID.

Court table:
| ID | DefendantID | CountyID |
| ------------ | ------------ | ------------ |
| 00035ed9 | 2a1e50f9 | Haus County |
| 000b2a8c | F3dc3251 | Saul County |

Pending Charge table:

ID	DefendantID	CourtID	Class
9f78778d	2a1e50f9	00035ed9	3
a53a4841	2a1e50f9	00035ed9	2
a9d2c5a2	F3dc3251	000b2a8c	A
daf26cb0	F3dc3251	000b2a8c	A

Desired Output:

County	Number Felony	Number Misdemeanor
Haus County	1	0
Saul County	0	1

I too will fiddle with what you already provided which is great. Maybe by some miracle I get to where I want to go first!?. Thanks again for all your assistance.

sql-server

来源：https://stackoverflow.com/questions/77398760/how-to-count-classify-specific-records-in-a-one-to-many-relationship

1条答案

按热度按时间

piztneat1#

You can use a subquery or CTE as a helper. ie:

select ChargeType, count(*)
from (
   Select case 
      when exists (select * from Charges ch 
                   where ch.CourtId = c.CourtId and ch.Class in ('A','B', 'C')) 
      then 'Misdemeanors' else 'Felony' end as ChargeType
from Courts c
) tmp
group by ChargeType;

EDIT:

WITH tmp AS (SELECT CountyID, CASE WHEN EXISTS 
  (SELECT *
   FROM PendingCharge AS pc
   WHERE c.ID=pc.CourtID AND pc.Class IN ('M', 'X', '1', '2', '3', '4')) 
      THEN 'Felony' 
      ELSE 'Misdemeanor' 
   END AS ChargeType
   FROM Court AS c)
SELECT CountyID, 
  SUM(CASE WHEN ChargeType='Felony' THEN 1 ELSE 0 END) AS [Number Felony], 
  SUM(CASE WHEN ChargeType='Misdemeanor' THEN 1 ELSE 0 END) AS [Number Misdemeanor]
FROM tmp
GROUP BY CountyID;

And here is DBFiddle demo.

展开查看全部

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server How to count/classify specific records in a one to many relationship?

1条答案

相关问题

热门标签

最新问答