CODEWITHSUNDEEP
sqlclickhouse
GreatFilter
GreatFilter

Reputation: 49

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

Upvotes: 0

Views: 35

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

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;

Upvotes: 1

Related Questions