Add calculated row in SQL: SUM/AVG/COUNT/etc

Programming
,
1

I need to calculate the percentage of Confirmed vs Preliminary in a table generated by SQL in Athena. The fenced code block below shows the table.

Calculation   england_0to1_weeks  england_1to2_weeks
Preliminary                  17                   19
Confirmed                    10                   16

I would like to add a row at the bottom of the table that calculates the percentage of Confirmed vs Preliminary (formula is Confirmed/Preliminary * 100):

Calculation   england_0to1_weeks  england_1to2_weeks
Preliminary                  17                   19
Confirmed                    10                   16
Percentage                   59                   84

I have tried using union all, pivot and unpivot but may be using these wrong. Any help would be hugely appreciated!

2

You can achieve the desired result using the UNION ALL operator in SQL. Here’s the SQL query to add the percentage row at the bottom of the table:

SELECT Calculation, england_0to1_weeks, england_1to2_weeks
FROM YourTable
UNION ALL
SELECT 'Percentage', 
       (Confirmed.england_0to1_weeks / Preliminary.england_0to1_weeks) * 100,
       (Confirmed.england_1to2_weeks / Preliminary.england_1to2_weeks) * 100
FROM YourTable Preliminary
JOIN YourTable Confirmed ON Preliminary.Calculation = 'Preliminary' AND Confirmed.Calculation = 'Confirmed'

Replace YourTable with the actual name of your table in Athena. This query will add a row called “Percentage” at the bottom of the table, and the values in the columns will be calculated using the formula Confirmed/Preliminary * 100.