# Combine multiple similar queries into one

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 */
``````
• Which version of Postgres? Commented Mar 6, 2015 at 16:35
• @ypercube I'm using 9.4 Commented Mar 6, 2015 at 17:20
• If that's correct: `All the metric columns have a value from 1 to 5.`, then you don't have a problem, because all results are the same. Please clarify: how selective are the predicates `metric_n BETWEEN 1 AND 5`? Commented Mar 7, 2015 at 4:19
• @ErwinBrandstetter yes, each metric column can only have a value in the range of 1 to 5. But I don't understand how that changes anything? Commented Mar 8, 2015 at 20:07
• The predicate `metric_1 BETWEEN 1 AND 5` is `TRUE` for every row and therefore irrelevant. The result is the same for every `metric_n`. Commented Mar 8, 2015 at 22:17

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
``````
• That's very cool. Thanks. But I don't understand something: The user can have a different response for each metric type. How do we account for that? So mertric_1 can be 4, and metric_2 can be 6, etc... I think I'm missing something... Commented Mar 6, 2015 at 16:15
• do you mean where clause in inline SurveyNormalised view?
– vmds
Commented Mar 6, 2015 at 16:42
• Ahh, never mind! I understand now. Thanks. Wouldn't it be more efficient to move the `WHERE (var BETWEEN 0 AND 10)` outside of the select clauses, just below the `GROUP BY`, since it's applicable to the entire query? Commented Mar 6, 2015 at 17:19

Another way to solve this:

``````WITH cte AS
( SELECT
q.query_id,
COUNT(d.id) AS sample_size,
SUM(d.weight) AS weight,
SUM(CASE WHEN d.var BETWEEN 9 AND 10 THEN d.pond END) AS promoter,
SUM(CASE WHEN d.var BETWEEN 0 AND 6  THEN d.pond END) AS detractor,
MAX(d.week) AS max_week
FROM
generate_series(1, 3)      -- change 3 to the number of different queries
AS q (query_id)
CROSS JOIN
surveys AS s
LEFT JOIN LATERAL
( SELECT 1, s.*
WHERE (metric_1 BETWEEN 1 AND 5)  --- query 1
UNION ALL
SELECT 2, s.*
WHERE (metric_2 BETWEEN 3 AND 7)  --- query 2
UNION ALL
SELECT 3, s.*
WHERE (metric_3 BETWEEN 6 AND 9)  --- query 3
--- etc
)
AS d (query_id, id, var, weight, metric_1, metric_2, week)
ON d.query_id = q.query_id
WHERE
(s.var BETWEEN 0 AND 10)
GROUP BY
q.query_id
)
SELECT
query_id,
weight, sample_size, detractor, max_week,
(promoter * 100) / weight AS promoter_p,
(promoter - detractor) * 100 / weight AS score
FROM
cte
ORDER BY
query_id ;
``````