[Solution] Aggregating Data by Multiple Fields

Click here to view the question


Solution:

Apply a limit and offset with an order by to return the second fastest runner

select
  platform
  , source
  , count(*)
from
  all_users
group by
  platform
  , source
order by
  platform
  , source

Concepts Covered: Aggregates, Group by

Explanation:

We first want to determine the columns we want shown. Here we want a platform, source, and the number of the users that belong to that platform-source combination. For the first two columns, we call the platform and source fields, separated by a comma. To pull the count, we use the count aggregate function. Note that the * is shorthand for "count all the rows." The same result can be achieved with count(1).

Since we have a mix of aggregates (the count function) and non aggregates (the platform and source fields) in our select list, we must apply a group by. In the group by, we need to list all the non-aggregated columns from our query. In this case, this is platform and source. The group by can be read as "count all the rows, bucketing them into categories based on their platform and source."

Lastly we applied an order by so all the rows of the same platform are grouped together in a more readable format.

Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
Like1 Follow
  • 1 Likes
  • 10 mths agoLast active
  • 315Views
  • 2 Following