I am new to SQL and doing the learning via datacamp. I was wondering if you can achieve the same result with 'HAVING' as with a nested 'WHERE' clause.
Related: SQL - having VS where I understand that HAVING is used with aggregate functions such as min, max, ..
How could I rewrite the following with HAVING?:
SELECT *
FROM populations
WHERE year = 2015 AND life_expectancy >(
SELECT AVG(life_expectancy)*1.15
FROM populations
);
Suppose I have 6 columns in the table 'populations': A (character), B (character), C (Character), D (number, i.e. life_expectancy as in the example), year and E (number).
I tried the following:
SELECT *
FROM populations
WHERE year = 2015
GROUP BY A, B, C, year
HAVING life_expectancy > AVG(life_expectancy)*1.15;
However, this returns an empty table. I realise it's not a reproducible example, but perhaps a general explanation as to why it might not be working would suffice :)
HAVING is generally used to limit the results of a GROUP BY clause, in the same way that a WHERE is used to limit the results of a SELECT clause.
In addition, while a WHERE clause cannot contain aggregates directly, you can either use a HAVING clause (with the aggregates), or you could use a sub-query in the WHERE clause which contains the aggregates.
Unless you are grouping your data, or otherwise aggregating it, then I cannot see why you would want to rewrite your WHERE clause as a HAVING clause.
In your example, you are creating a set of groups, based on A, B, C and Year. Then, you are limiting those groups to the ones where the life-expectancy of the group (whatever this is meant to be?) is greater than the average life-expectancy of that same group multiplied by 1.15. It doesn't make any sense, which is what you had already established.