mysql How do you count up each string value for each year?

mgdq6dx1 于 2022-12-22 发布在 Mysql

关注(0)|答案(1)|浏览(117)

Trying to figure out how to count each crime for each year to see which years the most crimes occurred, 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:

mysql

来源：https://stackoverflow.com/questions/74855942/how-do-you-count-up-each-string-value-for-each-year

1条答案

按热度按时间

t0ybt7op1#

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

赞(0）回复(0）举报 2022-12-22

我来回答

mysql How do you count up each string value for each year?

1条答案

相关问题

热门标签

最新问答