Group Computed Columns

We've learned how to make computed columns. It is possible to GROUP BY computed columns.

In Pages Visited, you grouped the sessions table by day to show a growth report.

What if we wanted a similar chart, but grouped by month instead of day. We can't GROUP BY started_at directly. We need to perform a computation on it to extract a year and a month. We can do that computation with extract().

SELECT
  extract(YEAR FROM started_at) AS session_year,
  extract(MONTH FROM started_at) AS session_month,
  sum(pages_visited) AS total_pages_visited
FROM sessions
GROUP BY 1, 2
ORDER BY 1, 2;

Notice that we use the column reference shortcut here.

Aggregate Computed Values

It is also possible to put aggregations into computed columns. Remember the daters table which stored people's height in centimeters?

Suppose we want to calculate the average height of all the users. We know how to do that with avg(). However, what if we want to show this result to Americans? We'll need to convert to inches, which is a simple division by 2.54.

SELECT
  avg(height_cm) AS avg_height_cm,
  avg(height_cm / 2.54) AS avg_height_in,
  avg(height_cm) / 2.54 AS avg_height_in
FROM daters;

Notice that we can put that division either inside or outside the call to avg(). This corresponds to converting to inches first and then averaging or averaging first and then converting. They are equivalent in this case, but both are an option.