How to calculate a percentage of a percentage in a single query

user_18321985 0
Viewed 67

Using databricks SQL I created a table that looks like this:

tablename RunDate Quality qaflag Status
blah 2022-06-02 bronze 1 Passed
blah1 2022-06-02 silver -1 failed

I can write a query that will calculate the percentage of each Quality type in the table e.i.

SELECT Quality, 
       COUNT(Status)*100/(SELECT COUNT(Status) FROM test) as Percentage
FROM test
GROUP BY Quality

That will give me this output:

Quality PercentageTotal
Silver 64
Gold 3
Bronze 33

I would like to add a percentage of each Quality that passed or failed as well. Basically, I need to try to get it to look like this:

Quality PercentageTotal PercentagePassed PercentageFailed
Silver 64 99 1
Gold 3 87 3
Bronze 33 60 40

What the table is saying is: Silver Tables constitute 64 percent of all tables tested, and 99 percent of them passed and 1 percent failed. (and so on for the other ones)

I am stuck at trying to figure out how to calculate the PercentagePassed/Failed can anyone help?

databricks-sql apache-spark-sql
1 Answers

Switch to a window function with distinct to eliminate your group by needs, then use a case expression:

SELECT distinct Quality
    , count(Status) over (partition by Quality) * 100 / count(status) over (partition by null) PercentageTotal
    , count(case when Status = 'Passed' then 1 end) over (partition by Quality) * 100 / count(status) over (partition by Quality) PercentagePassed
    , count(case when Status = 'Failed' then 1 end) over (partition by Quality) * 100 / count(status) over (partition by Quality) PercentageFailed
FROM test

You may want to consider switching to another data type that will support decimals, but I've left it as is for your example.

user_125712410
Related