Using Count case

Question

So I've been just re-familiarizing myself with SQL after some time away from it, and I am using Mode Analytics sample Data warehouse, where they have a dataset for SF police calls in 2014.

For reference, it's set up as this:

incident_num, category, descript, day_of_week, date, time, pd_district, Resolution, address, ID

What I am trying to do is figure out the total number of incidents for a category, and a new column of all the people who have been arrested. Ideally looking something like this

Category,  Total_Incidents,  Arrested
-------------------------------------
Battery         10              4
Murder          200             5

Something like that..

So far I've been trying this out:

SELECT category, COUNT (Resolution) AS Total_Incidents, (
    Select COUNT (resolution)
    from tutorial.sf_crime_incidents_2014_01
    where Resolution like '%ARREST%') AS Arrested
from tutorial.sf_crime_incidents_2014_01
group by 1
order by 2 desc

That returns the total amount of incidents correctly, but for the Arrested, it keeps printing out 9014 Arrest

Any idea what I am doing wrong?

Answer 1

The subquery is not correlated. It just selects the count of all rows. Add a condition, that checks for the category to be equal to that of the outer query.

SELECT o.category,
       count(o.resolution) total_incidents,
       (SELECT count(i.resolution)
               FROM tutorial.sf_crime_incidents_2014_01 i
               WHERE i.resolution LIKE '%ARREST%'
                     AND i.category = o.category) arrested
       FROM tutorial.sf_crime_incidents_2014_01 o
       GROUP BY 1

Answer 2

You could use this:

SELECT category, 
    COUNT(Resolution) AS Total_Incidents, 
    SUM(CASE WHEN Resolution LIKE '%ARREST%' THEN 1 END) AS Arrested
FROM tutorial.sf_crime_incidents_2014_01
GROUP BY category
ORDER BY 2 DESC;