Is there a way to have a one row result for multiple conditions?-CodePudding

I have to return an average result for multiple columns, only when another column is set to a specific value.

Here's an example of what I want:

SELECT AVG(col1_value1) AS res1 FROM table WHERE col1_value2 = 10;
SELECT AVG(col2_value1) AS res2 FROM table WHERE col2_value2 = 10;
SELECT AVG(col3_value1) AS res3 FROM table WHERE col3_value2 = 10;

Then I want my result to return this

res1	res2	res3
10.5	8.5	9

Currently, the option used is a PHP loop, which isn't really the ideal. If there's another option, I would love to know about it. Thank you.

CodePudding user response：

Using conditional averaging, we can try:

SELECT
    AVG(CASE WHEN col1_value2 = 10 THEN col1_value1 END) AS res1,
    AVG(CASE WHEN col2_value2 = 10 THEN col2_value1 END) AS res2,
    AVG(CASE WHEN col3_value2 = 10 THEN col3_value1 END) AS res3
FROM yourTable;

This has the advantage over your current approach in that it requires only a single pass over your table, rather than 3 separate queries.