Learn

Sometimes, we want to GROUP BY a calculation done on a column.

For instance, we might want to know how many movies have IMDb ratings that round to 1, 2, 3, 4, 5. We could do this using the following syntax:

SELECT ROUND(imdb_rating), COUNT(name) FROM movies GROUP BY ROUND(imdb_rating) ORDER BY ROUND(imdb_rating);

However, this query may be time-consuming to write and more prone to error.

SQL lets us use column reference(s) in our GROUP BY that will make our lives easier.

  • 1 is the first column selected
  • 2 is the second column selected
  • 3 is the third column selected

and so on.

The following query is equivalent to the one above:

SELECT ROUND(imdb_rating), COUNT(name) FROM movies GROUP BY 1 ORDER BY 1;

Here, the 1 refers to the first column in our SELECT statement, ROUND(imdb_rating).

Instructions

1.

Suppose we have the query below:

SELECT category, price, AVG(downloads) FROM fake_apps GROUP BY category, price;

Write the exact query, but use column reference numbers instead of column names after GROUP BY.

Take this course for free

Mini Info Outline Icon
By signing up for Codecademy, you agree to Codecademy's Terms of Service & Privacy Policy.

Or sign up using:

Already have an account?