SQL Nested Query with distinct count

Question

I have a dilemma, and I'm hoping someone will be able to help me out. I am attempting to work on some made up problems from an old text book of mine, this isn't a question from the book, but the data is, I just wanted to see if I could still work in SQL, so here goes. When this code is executed,

SELECT COUNT(code_description) "Number of Different Crimes", last, first, 
code_description 
FROM
(
SELECT criminal_id, last, first, crime_code, code_description
FROM criminals 
JOIN crimes USING (criminal_id)
JOIN crime_charges USING (crime_id)
JOIN crime_codes USING (crime_code)
ORDER BY criminal_id
)
WHERE criminal_id = 1020
GROUP BY last, first, code_description;

I am provided with these results:

Number of Different Crimes   LAST            FIRST      CODE_DESCRIPTION             
                     1       Phelps          Sam        Agg Assault                    
                     1       Phelps          Sam        Drug Offense

Inevitably, I would like the number of different crimes to be 2 for each line since this criminal has two unique crimes charged to him. I would like it to be displayed something like:

Number of Different Crimes   LAST            FIRST      CODE_DESCRIPTION             
                     2       Phelps          Sam        Agg Assault                    
                     2       Phelps          Sam        Drug Offense

Not to push my luck but I would also like to get rid of the follow line also:

WHERE criminal_id = 1020

to something a little more elegant to represent any criminal with more than 1 crime type associated with them, for this case, Sam Phelps is the only one in this data set.

If I'm understanding correctly, perhaps you can remove your group by clause, and instead use count(code_description) over (partition by last, first)? — sgeddes
– sgeddes, Commented Mar 1, 2015 at 1:40
Thank you for commenting @sgeddes! I attempted your method and received an error stating 'SQL command not properly ended'. — Adam
– Adam, Commented Mar 1, 2015 at 1:51

Alex Poole · Accepted Answer · 2015-03-01 10:57:54Z

1

As @sgeddes said in a comment, you can use an analytic count, which doesn't need a subquery if you're specifying the criminal ID:

SELECT COUNT(code_description) OVER (PARTITION BY first, last) AS "Number of Different Crimes",
  last, first, code_description 
FROM criminals 
JOIN crimes USING (criminal_id)
JOIN crime_charges USING (crime_id)
JOIN crime_codes USING (crime_code)
WHERE criminal_id = 1020;

If you want to look for anyone with multiple crimes then you do need a subquery so you can filter on the analytic result:

SELECT charge_count AS "Number of Different Crimes",
  last, first, code_description
FROM (
  SELECT COUNT(DISTINCT code_description) OVER (PARTITION BY first, last) AS charge_count,
    criminal_id, last, first, code_description 
  FROM criminals 
  JOIN crimes USING (criminal_id)
  JOIN crime_charges USING (crime_id)
  JOIN crime_codes USING (crime_code)
)
WHERE charge_count > 1
ORDER BY criminal_id, code_description;

SQL Fiddle demo.

If the charges are across multiple crimes, but duplicated, then the distinct count still works, but you might want to make add a distinct to the overall result set - unless you want to show other crime-specific info - otherwise you get something like this.

answered Mar 1, 2015 at 10:57

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

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

1 Comment

Adam Over a year ago

THANK YOU!! Your suggestion worked as instructed. I appreciate you taking the time to help me out! @Alex

Collectives™ on Stack Overflow

SQL Nested Query with distinct count

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related