CODEWITHSUNDEEP
sql
cluelessmoron
cluelessmoron

Reputation: 25

SQL cant use AVG in CASE condition

Hi everyone I was wondering if anyone could help me understand what I'm doing wrong, this query only returns one row when there are 100 rows in the table:

SELECT country, population, CASE
  WHEN population > AVG(population) THEN "Above average Population"
  ELSE "Below average Population"
  END as "population_avg_status"
FROM countries_by_population;

I noticed that if I change AVG(population) with the actual value of AVG(population) I get 100 rows so I'm guessing the problem is the AVG function.

Upvotes: 0

Views: 208

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

Use window functions:

SELECT country, population,
       (CASE WHEN population > AVG(population) OVER ()
             THEN 'Above average Population'
             ELSE 'Below average Population'
        END) as population_avg_status
FROM countries_by_population;

Some words about quotes:

  • Your code shows a clear confusion about quotes. You have used double quotes for both string values and a column alias.
  • The SQL standard string delimiter is the single quote ('), not the double quote ("). Although some databases extend the functionality of double quotes to delimit strings, it is better to stick with the standard.
  • Don't quote identifiers if they do not need to be. Your column name does not need to be quoted.

Upvotes: 0

Nick
Nick

Reputation: 147206

AVG is an aggregation function, and when an aggregation function is used without a GROUP BY clause it causes the result to be aggregated over all rows. That is why you are only getting one row in your output. To work around this, compute the average in a subquery:

SELECT country, 
       population, 
       CASE WHEN population > (SELECT AVG(population) FROM countries_by_population) THEN "Above average Population"
            ELSE "Below average Population"
       END as population_avg_status
FROM countries_by_population;

Upvotes: 1

Related Questions