Home How do I get rows in SQL that have a value in a column that is 20 percent higher than the column average?

Questions

How do I get rows in SQL that have a value in a column that is 20 percent higher than the column average?

March 11, 2024

So I am working in an SQL tutorial that is asking to find the states that have percentage of impaired drivers that is 20% higher than the average across all states. The percentage is represented by percentage points between 0-100.

I wrote the following query:

SELECT state, percent_alcohol_impaired 
FROM playground.bad_drivers
WHERE percent_alcohol_impaired - 20 >
        (SELECT AVG(percent_alcohol_impaired)
         FROM playground.bad_drivers)
LIMIT 100

The tutorial did not execute my query. Is this not a good way (or is there a better way) to get states above the average? TIA.

>Solution :

SELECT state, percent_alcohol_impaired  
FROM playground.bad_drivers 
WHERE percent_alcohol_impaired > 1.2 * (SELECT AVG(percent_alcohol_impaired) FROM playground.bad_drivers) 
LIMIT 100;

You should multiply the average percentage by 1.2 to find 20% higher than the average, and then compare it to the percentage of impaired drivers in each state.