Postgresql – Calculating the number of times a motive was cited over a given period

pivotpostgresql

I'm using PostgreSQL 9.4. I have the following surveys table. Each row includes a motive that was cited by the respondent, represented by the motive_value column (think of it as motive_id).

---------------------------------------
id | var      | motive_value | period |
---------------------------------------
1  | 9        | 1            | 1      |
2  | 7        | 2            | 2      |
3  | 2        | 5            | 1      |
4  | 3        | 3            | 1      |
5  | 6        | 1            | 2      |
---------------------------------------

And motives table. There are around 20 motives, but for the sake of brevity I'm including five.

-----------------------------
id | value     | name       |
-----------------------------
1  | 1        | Blocked     |
2  | 2        | Delivery    |
3  | 3        | Forgotten   |
4  | 4        | Info        |
5  | 5        | Other       |
-----------------------------

So, I need to get, grouped by period descending, the top 5 most cited motives. ( note that, most cited motives for the entire surveys table, and not for a given period).

Here's a table that represents what I'm after.

------------------------------------------------------------------
| period | total | blocked | delivery | forgotten | info | other |
------------------------------------------------------------------
| 2      | 2     | 1       | 1        | 0         | 0    | 0     |
| 1      | 3     | 1       | 0        | 1         | 0    | 1     |
------------------------------------------------------------------

The total is the number respondents, or survey rows, in a given period. It's not necessary, but it's nice to have. I'd prefer not to have it if it adds lots of complexity. The other columns represent the motives, and the number of times each motive was cited. For example, in period 1 there were 3 surveys, and one of them cited the blocked motive.

I started with this query. It gets me the top 5 most cited motives.

SELECT
    motive_value,
    motives."name" AS motive,
    COUNT(motive_value) AS sample_size
FROM surveys
INNER JOIN motives ON surveys.motive_value = motives."value"
WHERE var BETWEEN 0 AND 10
GROUP BY motive_value, motive
ORDER BY sample_size DESC
LIMIT 5

I also wrote this query for one motive. It brings me, grouped by period, the number of times a motive was cited.

SELECT
    period,
    COUNT(motive_value) AS sample_size,
    motives."name" AS motive
FROM surveys
INNER JOIN motives ON surveys.motive_value = motives."value"
WHERE (var BETWEEN 0 AND 10) AND motives_value = 1
GROUP BY period, motive
ORDER BY period DESC

The trouble is I need one query for each one of the top 5 motives in the first query. I also need the motives as columns, as shown in my example table. So I'm not sure how to combine those queries, or even create the table where motives are columns.

Best Answer

I think I might have figured this out using Postgresql's array_agg, but with a big downside, so I'd still be interested in seeing other answers.

SELECT
    period,
    ("array_agg"(ROW(motive_value, motive_name, sample_size)))[1:5] AS sample_list
FROM
    (
        SELECT
            period,
            motive_value,
            COUNT(motive_value) AS sample_size,
            motives."name" AS motive_name
        FROM surveys
        INNER JOIN motives ON surveys."motive_value" = motives."value"
        WHERE var BETWEEN 0 AND 10
        GROUP BY period, motive_value, motive_name
        ORDER BY sample_size DESC
    ) AS row
GROUP BY period
ORDER BY period DESC
LIMIT 6

The only down side is that, instead of columns, I get an array like this:

-----------------------------------------------------------------------------
| period | sample_list                                                      |
-----------------------------------------------------------------------------
| 2      | {"(1,\"Blocked\",1)", "(2,\"Delivery\",1)", ... }                |
| 1      | {"(1,\"Blocked\",1)", "(3,\"Forgotten\",1)", "(5,\"Other\",1)" } |
-----------------------------------------------------------------------------
Related Question