how to divide two rows-CodePudding

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

CodePudding user response：

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;