Using subqueries to aggregate in multiple stages

Hi ya'll,

I had a question on the advanced section of the SQL tutorial. The question is,

Using subqueries to aggregate in multiple stages

What if you wanted to figure out how many incidents get reported on each day of the week? Better yet, what if you wanted to know how many incidents happen, on average, on a Friday in December? In January? 

 

I was wondering if my code works without doing a subquery?

Thanks

SELECT day_of_week, date, COUNT(incidnt_num) as Number_Of_Incidents
  FROM tutorial.sf_crime_incidents_2014_01
    WHERE LEFT(date, 2) = '12'
    AND day_of_week = 'Friday'
      GROUP BY day_of_week, date
      ORDER BY date
1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi DevLifee, thanks for reaching out! Your query correctly counts the number of incidents on Fridays in December. Now, to get the average of those counts, you'll have to use this query as a subquery. Here is one solution:

    SELECT AVG(number_of_incidents)
    FROM
    (SELECT day_of_week, date, COUNT(incidnt_num) AS Number_Of_Incidents
    FROM tutorial.sf_crime_incidents_2014_01
    WHERE LEFT(date, 2) = '12'
    AND day_of_week = 'Friday'
    GROUP BY day_of_week, date
    ORDER BY date) AS counts
    Reply Like
Like Follow
  • Status Answered
  • 1 yr agoLast active
  • 1Replies
  • 162Views
  • 2 Following