-1

I am trying to find how many times the primary_type of 'crime' happened each year.

The Dataset is on KAGGLE.

https://www.kaggle.com/datasets/currie32/crimes-in-chicago

and I downloaded it and uploaded it into BigQuery.

SELECT DISTINCT primary_type FROM bigquery-public-data.chicago_crime.crime

https://i.stack.imgur.com/ThBZK.jpg

I ran this query and I know that their are 36 different primary_type's of crimes that are linked with specific codes the primary key for the codes is "iucr". But, I just want to count how many times each of the separate 36 primary_type show up each year. Can someone help me?

SELECT DISTINCT primary_type FROM bigquery-public-data.chicago_crime.crime ORDER BY year

I tried this but it gave me an error saying, "ORDER BY clause expression references column year which is not visible after SELECT DISTINCT at [6:3]".

Do I need to do a subquery? I'm still learning those.

Krsna's Shinobi
  • 1
  • 1

1 Answers1

1

To count how many of each types happen for every year you could use something like this.

SELECT
    primary_type,
    year,
    count(*)
FROM
    `bigquery-public-data.chicago_crime.crime`
GROUP BY
    primary_type,
    year
ORDER BY 
    primary_type,
    year

It will show you number of each type for each year present in your table.

Take a note, that if crime of some type didn't happen in any year you wouldn't see 0, but rather row for this crime in that year will be lacking.

markalex
  • 8,623
  • 2
  • 7
  • 32
  • I there a way to consolidate primary_types, for example instead of showing the same primary type for each year with 21 rows and a matching date. But show 1 primary_type per row with dates like 2001 to 2023? – Krsna's Shinobi Mar 25 '23 at 01:05
  • Sure, you can use [PIVOT](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#pivot_operator). Explanation with examples [here](https://towardsdatascience.com/pivot-in-bigquery-4eefde28b3be) – markalex Mar 25 '23 at 07:37