SQL: Perform calculation across rows, creating a new row for the result

Question

I did some summarizing and counting of different values w/ GROUP BY and got the following result:

DATE | Parameter | Count 
2000 | Desired   | 10
2000 | Reality   | 9
2001 | Desired   | 17
2001 | Reality   | 17
etc.

Now I want to calculate the percentage Reality / Desired of each year and store the result in a new row with parameter called Percentage. Just like:

DATE | Parameter | Count 
2000 | Desired   | 10
2000 | Reality   | 9
2000 | Percentage| 90
2001 | Desired   | 17
2001 | Reality   | 17
2001 | Percentage| 100
etc.

I have already thought about some combinations out of joins, window function for calculating percentage and create the new row with CTE and UNION ALL, but I am stuck and need some inspiration for ideas.

Thank you very much!

Answer 1

One method uses conditional aggregation:

select date, parameter, count
from t
union all
select date, 'Percentage',
       ( sum(case when parameter = 'Reality' then count end) /
         sum(case when parameter = 'Desired' then count end)
       )
from t
group by date;

I should note that you can insert the results from the second query if you want to actually add the rows into the table.