Having Statement and Where Statement producing inconsistent results regarding Pokemon data

this is my first question. Go easy please!

I am doing an SQL project in KhanAcademy. My source data are lines 1-181 of this pokemon database (that is, all gen 1 pokemon), and I am running some queries regarding the attack stats of the pokemon.

Running SELECT Type_1, COUNT(*) FROM pokemon WHERE "Attack" > 120 GROUP BY Type_1;, I get 15 Total Pokemon grouped by 9 different types, which is what I expect.

However, when I attempt a HAVING statement to find the number of types of Pokemon with at least one pokemon with over 120 Attack, it returns only 4 types.

This is the code: SELECT Type_1 AS "type" FROM pokemon GROUP BY type_1 HAVING "Attack" > 120;.

To show an example, one pokemon that is missing from my 2nd query is Machamp: INSERT INTO pokemon(Number,Name,Type_1,Type_2,Total,HP,Attack,Defense,Sp_Atk,Sp_Def,Speed,Generation,Legendary) VALUES (68,'Machamp','Fighting',NULL,505,90,130,80,65,85,55,1,'False');

I'm not sure what is wrong from my 2nd query. Please help if you can.

Solution

This query:

SELECT Type_1 AS "type"
FROM pokemon
GROUP BY type_1
HAVING "Attack" > 120;

is not valid SQL and should be returning an error. Why? Because the HAVING takes place after the GROUP BY. And Attack is not defined. It would need to be in an activation function such as:

HAVING MAX(Attack) > 120

This version of the query is logically equivalent to:

SELECT p.type
FROM (SELECT Type_1 AS "type", MAX(Attack) as max_attack
      FROM pokemon
      GROUP BY type_1
     ) p
WHERE max_Attack > 120