I am trying to write a sql query for which crime is max in each district

Asked Jan 16 '23 at 12:10

Active Jan 16 '23 at 12:23

Viewed 24 times

I was asked to print the maximum crime along with the count that is taking place in each district which I have to write using only subqueries.

This is the the code that I tried:

select  DISTRICT, max(b) max_crime from
(select distinct count(offense_code_group)b,DISTRICT  from
 dbo.crime
group by DISTRICT,OFFENSE_CODE_GROUP)a
group by DISTRICT
;

By executing this, the output I got was this:
output of the query after executing

I need the name of the crime(offense_code_group) along with count. Actual output:- district count offense_code_group A1 3496 Larceny

edited Jan 16 '23 at 12:23

asked Jan 16 '23 at 12:10

Tejasvi Kommuri

What is a "maximum crime"? – Thom A Jan 16 '23 at 12:12
@Larnu thanks for responding but I got the count of maximum crime but need to get the name . for eg:- district A1 has larency as max crime i should get Larency also in the output along with its count – Tejasvi Kommuri Jan 16 '23 at 12:14
See the linked duplicate, which explains how to get the "top" row in each group. – Thom A Jan 16 '23 at 12:15

I am trying to write a sql query for which crime is max in each district

0 Answers0