The problem in your code is in the line:
AND year IN (2001, 2018)
Here you selected only 2001 and 2008, instead, you should have selected all the years between 2001 and 2018. You should replace the above command with:
AND year IN (2001, 2018)
I hope this would work for you.
SELECT
primary_type,
COUNT(arrest) arrest_total,
COUNTIF(year = 2015) AS arrests_2015,
COUNTIF(year = 2016) AS arrests_2016
FROM `bigquery-public-data.chicago_crime.crime`
WHERE arrest = TRUE
AND year BETWEEN 2001 AND 2018
AND primary_type NOT IN ('OTHER OFFENSE', ' all non-criminal types')
GROUP BY primary_type, arrest
Also, to include non-arrests in your data table run the following code:
#standardSQL
SELECT
primary_type,
arrest,
COUNT(arrest) arrest_total,
COUNTIF(year = 2015) AS arrests_2015,
COUNTIF(year = 2016) AS arrests_2016
FROM `bigquery-public-data.chicago_crime.crime`
WHERE year BETWEEN 2001 AND 2018
AND primary_type NOT IN ('OTHER OFFENSE', ' all non-criminal types')
GROUP BY primary_type, arrest
And, for getting one output row per primary type, write the below command:
SELECT
primary_type,
COUNTIF(arrest) arrests,
COUNTIF(NOT arrest) non_arrests,
COUNT(arrest) arrest_total,
COUNTIF(year = 2015) AS arrests_2015,
COUNTIF(year = 2016) AS arrests_2016
FROM `bigquery-public-data.chicago_crime.crime`
WHERE year BETWEEN 2001 AND 2018
AND primary_type NOT IN ('OTHER OFFENSE', ' all non-criminal types')
GROUP BY primary_type
If you want more information regarding Hadoop, refer to the following video: