How do you count up each string value for each year?-CodePudding

Trying to figure out how to count each crime for each year to see which years the most crimes occured.USING MYSQL

This code is giving me the number of times each distinct crime is happening per year.

SELECT year_time, offense_category, COUNT(*) AS crime_incidents
FROM rms_crime_incidents
GROUP BY year_time, offense_category
ORDER BY year_time DESC, offense_category ASC, crime_incidents ASC;

I want the total number of crimes per year.

These are my current results

    year_time   offense_category    crime_incidents
    2022            AGGRAVATED ASSAULT  14
    2022            DAMAGE TO PROPERTY  7
    2022            OBSTRUCTING JUDICIARY   2
    2021            AGGRAVATED ASSAULT  443
    2021            BURGLARY            204
    2021            DAMAGE TO PROPERTY  460
    2021            DANGEROUS DRUGS         59
    2021            DISORDERLY CONDUCT  14
    


I am expecting

    year_time   total_crime_incidents
2022            140
2022            789
2022            254
2021            443
2021            204
2021            460
2021            590
2021            126


[SAMPLE DATA][1]


  [1]: https://i.stack.imgur.com/TTTqm.png

CodePudding user response：

You can remove the offense_category from the select list and the group by clause so the query only groups by year:

SELECT   year_time, COUNT(*) AS total_crime_incidents
FROM     rms_crime_incidents
GROUP BY year_time
ORDER BY year_time DESC