MySQL Select Count Distinct

Question

I am on MySQL and having trouble calculating totals.I am trying to figure out an SQL query which will return the correct result using the following data and query. I want to get a count of total cases. In my result from my query the F and I counts are right the M case count is off. If the case has an F then it should be counted as an F case only not also an M case which my query is doing.

Data:

BKNUM      CG             Charge Description            Severity
123456      1       POSS CONT SUB W/I MAN/DEL/SELL          F
123456      1       POSS LEGEND DRUG W/O PRESCRIPTION       M
654321      1       VIOLATION OF PROBATION     M            I
987654      1       AGGRAVATED ASSAULT                      F
987654      1       POSS OF HANDGUN WHILE INTOXICATED       M
987654      1       POSSESSION OF PROHIBITED WEAPON         M
876543      1       VIOLATION OF PROBATION     M            I
765432      1       CRIM ATT-BURGLARY BUILDING              F
765432      1       POSSESSION OF BURGLARY TOOLS            M
234567      1       POSS MARIJUANA W/I MAN/DEL/SELL         F
234567      1       DRIVING WHILE LICENSE S/R/C             M
234567      1       IMPROPER DISPLAY OF REGISTRATION        M
345678      1       DRIVING WHILE LICENSE S/R/C             M
345678      1       EVADING ARREST                          M
345678      1       RESISTING OFFICIAL DETENTION            M
345678      1       UNLAWFUL POSS WEAPON                    M
345678      1       POSS OF CONT SUBSTANCE                  M
345678      1       CRIM ATT-POSS OF CONT SUB-MARIJUANA     M

Query:

Select Severity as 'Charge CLass',
Count(Distinct BKNUM, CG) as 'Total Cases',
Count(BKNUM) as 'Toatl Charges'
From test
group by severity

Result I get:

Charge Class        Total Cases     Total Charges
    F                     4             4
    I                     2             2
    M                     5            12

Result should be:

Charge Class        Total Cases     Total Charges
    F                     4             4
    I                     2             2
    M                     1             12

Any help would be greatly appreciated.

Can you elaborate on why you think there should only be 1 for 'M' class? 'M' is associated with 5 distinct BKNUMs... and CG are all the same, so that's just effectively 'distinct BKNUM' — Eric Haynes
– Eric Haynes, Commented Mar 5, 2015 at 3:56
Can you create an sql fiddle of your question here: sqlfiddle.com — Rigel1121
– Rigel1121, Commented Mar 5, 2015 at 3:59
Yes Erich the BKNUM and CG make up a unique case. There are multiple charges per case. If there is an F charge then it should be counted as an F case even if there is an M charge in the case. If there are only M charges then it should be counted as an M case. Does that make sense? — rgipson
– rgipson, Commented Mar 5, 2015 at 4:11
I tried to create a fiddle. I cant figure out how to get my ddl to work... — rgipson
– rgipson, Commented Mar 5, 2015 at 4:22

jpw · Accepted Answer · 2015-03-06 00:42:43Z

0

Not the most beautiful query, but I think it should work:

select t.severity, count(distinct bknum) "Total Cases", a.c "Total Charges" 
from test t 
join (select severity, count(*) c from test group by severity) a on t.severity = a.severity
where t.severity in ('i','f') 
   or (t.severity = 'm' and bknum not in (select bknum from test where severity = 'f'))
group by t.severity, a.c

Or maybe this:

select t.severity, count(distinct bknum, cg) "Total Cases", a.c "Total Charges" 
from test t 
join (select severity, count(*) c from test group by severity) a on t.severity = a.severity
where t.severity in ('i','f') 
   or (t.severity = 'm' and (bknum, cg) not in (select bknum, cg from test where severity = 'f'))
group by t.severity, a.c

edited Mar 6, 2015 at 0:42

answered Mar 5, 2015 at 4:49

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rgipson Over a year ago

This seems like it will work! However, The charge is made up of unique bknum and cg, not just bknum.

jpw Over a year ago

@rgipson Not sure I get what you mean, but I added another version that might be what you want. If it isn't please explain what's wrong.

rgipson Over a year ago

Thank you very much it seems to work! Yes that is it exactly I need both the BKNUM and CG to be taken into account because sometimes there can be a differnt CG on a BKNUM which should be counted as a seperate case.Sorry i did not put any data like that in my sample.

Community · Accepted Answer · 2017-05-23 12:28:24Z

0

You need to use something like GROUP BY on BKNUM and DISTINCT on CG column. See Distinct on multiple columns in MySQL for reference - it should lead you to the solution you are looking for.

edited May 23, 2017 at 12:28

CommunityBot

11 silver badge

answered Mar 5, 2015 at 4:08

smallworld

9507 silver badges15 bronze badges

Comments

Erik Blessman · Accepted Answer · 2015-03-05 04:25:10Z

0

SELECT CASE WHEN S = 1 THEN 'I' WHEN S = 2 THEN 'M' ELSE 'F' END
,    Qty
,    BKNUM
,    CG
FROM (
SELECT MAX(CASE WHEN Severity = 'I' THEN 1 WHEN Severity = 'M' THEN 2 ELSE 3 END) AS S
,    COUNT(*) AS Qty
,    BKNUM
,    CG
FROM Test
GROUP BY BKNUM, CG ) AS t

answered Mar 5, 2015 at 4:25

Erik Blessman

6934 silver badges11 bronze badges

Collectives™ on Stack Overflow

MySQL Select Count Distinct

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related