How to Combine multiple rows to single row based on a column in MySQL or Hive

user_1326784 0
Viewed 178

I need to generate average sales per Title between year 2019 to 2021. There are 2 input tables:

Title Table
Title_id Title_type Price_per
1        tv        10
2        book      50
3        cd        20
        
Transactions table(trans)
tran_id Title_id   Qty  year
1       3          2    2019
2       1          1    2019
3       3          5    2020
4       3          3    2020
5       1         10    2021

The expected result should generate below columns:

Title_id|Avg_sales_2019|Avg_sales_2020|Avg_sales_2021

title_id        avg_sales_2019  avg_sales_2020  avg_sales_2021
1               10.0            NULL            100.0
3               40.0            80.0            NULL

I used below query, but it does not generate the expected output

select a.title_id,
case when a.year=2019 then avg end as Avg_sales_2019,
case when a.year=2020 then avg end as Avg_sales_2020,
case when a.year=2021 then avg end as Avg_sales_2021
from (Select t.title_id, x.year, AVG(t.Price_per*x.Qty) as avg 
from title t join trans x on t.title_id=x.title_id 
group by t.title_id,x.year) a;

title_id        avg_sales_2019  avg_sales_2020  avg_sales_2021
1               10.0            NULL            NULL
1               NULL            NULL            100.0
3               40.0            NULL            NULL
3               NULL            80.0            NULL

How to combine the rows for a particular title_id to get the expected result

Note: I am running the query in Hive

hive db2 mysql sql database
1 Answers

Use conditional aggregation:

SELECT
    t.title_id,
    AVG(CASE WHEN x.year = 2019
             THEN t.Price_per * x.Qty ELSE 0 END) AS avg_sales_2019,
    AVG(CASE WHEN x.year = 2020
             THEN t.Price_per * x.Qty ELSE 0 END) AS avg_sales_2020,
    AVG(CASE WHEN x.year = 2021
             THEN t.Price_per * x.Qty ELSE 0 END) AS avg_sales_2021
FROM title t
LEFT JOIN trans x
    ON x.title_id = t.title_id
GROUP BY
    t.title_id
ORDER BY
    t.title_id;
user_18632290
Related