WHERE VS HAVING in SQL

1 
CREATE TABLE exercise_logs
    (id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT,
    minutes INTEGER, 
    calories INTEGER,
    heart_rate INTEGER);

INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES ("biking", 30, 115, 110);
INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES ("biking", 10, 45, 105);
INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES ("dancing", 15, 200, 120);
INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES ("dancing", 15, 165, 120);
INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES ("tree climbing", 30, 70, 90);
INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES ("tree climbing", 25, 72, 80);
INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES ("rowing", 30, 70, 90);
INSERT INTO exercise_logs(type, minutes, calories, heart_rate) VALUES ("hiking", 60, 80, 85);

SELECT * FROM exercise_logs;

SELECT type, SUM(calories) AS total_calories FROM exercise_logs GROUP BY type;

SELECT type, SUM(calories) AS total_calories FROM exercise_logs
    GROUP BY type
    HAVING total_calories > 150
    ;

In the above code, the last select will list you all the exercises where its total calories burned is more than 150.
How come I cannot use WHERE instead of having?
Is it because I am executing something on the new column? (SUM(calories))

When do I know I can use WHERE and when do I know I need to use HAVING instead?

2

@camperextraordinaire

(20 character thingy)

3

Because you need to filter records of a grouped result.

Yes

A WHERE clause is used is filter records from a result. The filter occurs before any groupings are made. A HAVING clause is used to filter values from a group.

You can have situations where you want to filter the selection before the groups are created. For example, if I wanted to list all the exercises where the total calories burned is more than 150 but did not want to include ‘dancing’ as a type, then I would write.

SELECT type, SUM(calories) as total_calories FROM exercise_logs
WHERE type <> 'dancing'
GROUP BY type
HAVING total_calories > 150;

You will notice the WHERE clause comes before the GROUP BY.

The above would produce;

type total_calories
biking 160
1 Like
4

thanks again, for the in depth explanation.