PostgreSQL – How to Combine Multiple Similar Queries into One

aggregatepostgresql

I need to calculate a weighted average using the following table. I have to calculate this number for metric_1 up to metric_10. All the metric columns have a value from 1 to 5.

------------------------------------------------------
id | var      | weight | metric_1 | metric_2 | week
------------------------------------------------------
1  | 9        | 0.56   | 4        | 2        | 8
2  | 3        | 1      | 3        | 1        | 7
3  | 7        | 0.98   | 3        | 5        | 8
------------------------------------------------------

Here's the query I'm using. I need same query for each metric, from 1 to 10.

SELECT
    weight, sample_size, detractor,
    (promoter * 100) / weight AS promoter_p,
    (promoter - detractor) * 100 / weight AS score
FROM
    (
        SELECT
            COUNT(surveys.id) AS sample_size,
            SUM(weight),
            SUM(CASE WHEN var BETWEEN 9 AND 10 THEN pond END) AS promoter,
            SUM(CASE WHEN var BETWEEN 0 AND 6  THEN pond END) AS detractor,
            MAX(week)
        FROM surveys
        WHERE (var BETWEEN 0 AND 10) AND (metric_1 BETWEEN 1 AND 5)
    ) t

I there a way to combine those queries instead of having one query for each metric? The only difference is the WHERE clause:

        WHERE (...) AND (metric_1 BETWEEN 1 AND 5) /* metric_2, ...10 */

Best Answer

metric columns are redundant in survey table. we can create a new metric type column to differentiate multiple metrics and to normalize survey table. we can even do this normalization in inline view.

following sample query may explain this. it use SurveyNormalized inline view and it take aggregate group by metricType.

CREATE TABLE #Surveys(id int, VAR int, [weight] float, metric_1 int, metric_2 int, [week] int)

INSERT INTO #Surveys VALUES(1, 9, 0.56 , 4, 2, 8), (2, 3, 1, 3, 1, 7), (3, 7, 0.98 , 3, 5, 8)

SELECT
    weight, sample_size, detractor,
    (promoter * 100) / weight AS promoter_p,
    (promoter - detractor) * 100 / weight AS score,
    t.metricType
FROM
    (
        SELECT
            COUNT(id) AS sample_size,
            SUM(weight) [weight],
            SUM(CASE WHEN var BETWEEN 9 AND 10 THEN 1 END) AS promoter,
            SUM(CASE WHEN var BETWEEN 0 AND 6  THEN 2 END) AS detractor,
            MAX(week) wk,
            metricType
        FROM (
                SELECT id, var, weight, metric_1 AS metric, 'metric_1' AS metricType, week
                FROM #Surveys
                WHERE (var BETWEEN 0 AND 10) AND (metric_1 BETWEEN 1 AND 5)
                UNION
                SELECT id, var, weight, metric_2 AS metric, 'metric_2' AS metricType, week
                FROM #Surveys
                WHERE (var BETWEEN 0 AND 10) AND (metric_2 BETWEEN 1 AND 5)
            )SurveyNormalised
            GROUP BY SurveyNormalised.metricType

    ) t
Related Question