CODEWITHSUNDEEP
sqlpostgresql
Jumbalaya Wanton
Jumbalaya Wanton

Reputation: 1621

Postgresql GROUP BY with a MAX condition

This query calculates a weighted average grouped by channel (region). I want to add an extra filter that only considers the most recent period for the calculation. The problem is that the most recent period is not known (its not a date column, rather an integer, and that's not my choice). So I have to work it out before I can use it.

I tried using AND MAX(period) in the WHERE clause, but Postgresql wont let me use aggregate functions in where clauses.

SELECT
    channel, weight, label,
    (promoter  * 100) / weight AS promoter_p,
    (passive   * 100) / weight AS passive_p,
    (detractor * 100) / weight AS detractor_p,
    (promoter - detractor) * 100 / weight AS nps
FROM
    (
        SELECT
            channel,
            regions.name AS label,
            COUNT(surveys.id) AS base,
            SUM(weight),
            SUM(CASE WHEN var BETWEEN 9 AND 10 THEN weight END) AS promoter,
            SUM(CASE WHEN var BETWEEN 7 AND 8  THEN weight END) AS passive,
            SUM(CASE WHEN var BETWEEN 0 AND 6  THEN weight END) AS detractor
        FROM surveys
        INNER JOIN regions ON regions.code = surveys.channel
        WHERE (var BETWEEN 0 AND 10) AND active = 1
        GROUP BY channel, label
        ORDER BY label ASC
    ) t

Upvotes: 0

Views: 156

Answers (2)

cur4so
cur4so

Reputation: 1820

SELECT
    channel, weight, label,
    (promoter  * 100) / weight AS promoter_p,
    (passive   * 100) / weight AS passive_p,
    (detractor * 100) / weight AS detractor_p,
    (promoter - detractor) * 100 / weight AS nps
FROM
    (
        SELECT
            channel,
            regions.name AS label,
            COUNT(surveys.id) AS base,
            SUM(weight) as weight,
            SUM(CASE WHEN var BETWEEN 9 AND 10 THEN weight END) AS promoter,
            SUM(CASE WHEN var BETWEEN 7 AND 8  THEN weight END) AS passive,
            SUM(CASE WHEN var BETWEEN 0 AND 6  THEN weight END) AS detractor
        FROM surveys
        INNER JOIN regions ON regions.code = surveys.channel
        INNER JOIN (SELECT MAX(period) as max from surveys) x ON period = max
        WHERE (var BETWEEN 0 AND 10) AND active = 1
        GROUP BY channel, label
        ORDER BY label ASC
    ) t

Upvotes: 1

Egor Lyashenko
Egor Lyashenko

Reputation: 797

Try to add this condition:

...
WHERE (var BETWEEN 0 AND 10) AND active = 1
AND period = (SELECT MAX(period) FROM surveys)

Upvotes: 2

Related Questions