Search code examples
sqlclickhouse

how to divide two rows

I have a table

date measure value
2022-12-09 A 10
2022-12-09 B 2
2022-12-03 A 300
2022-12-03 B 30

i need to have new rows C=A/B

date measure value
2022-12-09 A 10
2022-12-09 B 2
2022-12-09 C 5
2022-12-03 A 300
2022-12-03 B 30
2022-12-03 C 10

how it can be done

Solution

  • Using conditional aggregation along with a union we can try:

    SELECT date, measure, value FROM yourTable
UNION ALL
SELECT
    date,
    'C',
    MAX(CASE WHEN measure = 'A' THEN value END) /
    MAX(CASE WHEN measure = 'B' THEN value END)
FROM yourTable
GROUP BY date
ORDER BY date, measure;