Search code examples
sqlsql-servergroup-bycountgrouping

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

enter image description hereThanks 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.

Solution

  • 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.