How to pivot aggregated rows into columns-CodePudding

I have the following table:

 ------- ---------- ------ ------ 
| price | quantity | year | name |
 ------- ---------- ------ ------ 
|     5 |        1 | 2022 | x    |
|     7 |        3 | 2022 | y    |
|     5 |        2 | 2022 | x    |
|     5 |        3 | 2021 | x    |
|     4 |        2 | 2022 | y    |
|     8 |        3 | 2022 | z    |
 ------- ---------- ------ ------

I want sum of price divided by sum of quantity for 'x' and 'y' in year 2022. I tried the following query:

SELECT ROUND(SUM(price)/SUM(quantity), 2) x,
ROUND(SUM(price)/SUM(quantity), 2) y FROM orders 
WHERE name IN ('x', 'y') AND year = 2022 GROUP BY name;

Which gives me output as:

 ------ ------ 
| x    | y    |
 ------ ------ 
| 3.33 | 3.33 |
| 2.20 | 2.20 |
 ------ ------

but I want the output to be:

 ------ ------ 
| x    | y    |
 ------ ------ 
| 3.33 | 2.20 |
 ------ ------

CodePudding user response：

You can use conditional aggregation and then an outer-aggregation to remove the NULL values and condense into a single row:

select Max(x) x, Max(y) y
from (
select 
    case when name = 'x' then Round(SUM(price)/SUM(quantity), 2) end x,
    case when name = 'y' then Round(SUM(price)/SUM(quantity), 2) end y
    from Orders
    where name in ('x', 'y') AND year = 2022 
    group by name
)t;

CodePudding user response：

Reading your comments, I'd like to add that perhaps a function could be useful if this calculation is going to be recurrent.

DELIMITER ;;
CREATE FUNCTION example_function (
    p_name VARCHAR(32), p_year YEAR
)
RETURNS DOUBLE
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
BEGIN
    DECLARE result DOUBLE;
    SELECT 
        ROUND(SUM(price)/SUM(quantity), 2) INTO result
    FROM orders 
    WHERE name = p_name AND year = p_year;
    RETURN result;
END;;
DELIMITER ;

so you could query it like

SELECT
    example_function('x', 2022) as x,
    example_function('y', 2022) as y
FROM DUAL;

I'm not sure how performant this would be against the accepted answer though.