rw2 rw2 - 1 year ago 41
SQL Question

SQL Transpose column, with frequencies of unique combinations as new values

I am trying to use postgresql to summarise some data, and need to produce a frequency column, and then transpose another column with results from the frequencies.

My starting table is like this:

Month | Nationality | Car
Oct-15 | GBR | Rover
Sep-15 | FRA | Fiat
Oct-15 | GBR | Rover
Sep-15 | TUR | Fiat

I want to make a new column which counts the frequency of each unique combination of the other columns. So it would like something like this:

Month | Nationality | Car | FREQ
Oct-15 | GBR | Rover | 2
Sep-15 | FRA | Fiat | 1
Sep-15 | TUR | Fiat | 1

And I then want to transpose the Month column, creating new columns for each value in Month, and filling in the values for those columns using the frequency count:

Nationality | Car | Sep-15 | Oct-15
GBR | Rover | 0 | 2
FRA | Fiat | 1 | 0
TUR | Fiat | 1 | 0

I have been looking into doing pivot queries and the crosstab function for the transposing, but can't figure out how to get it to work using the frequency of unique combinations as the values.


Answer Source

One method uses conditional aggregation:

select nationality, car,
       sum(case when month = 'Sep-15' then 1 else 0 end) as "Sep-15",
       sum(case when month = 'Oct-15' then 1 else 0 end) as "Oct-15"
from t
group by nationality, car;

This formulation assumes that month is stored as a string and not a date.

Postgres does offer other capabilities for this purpose, such as crosstab. However, this seems like the simplest method for your case.