Can HAVING accept 1,2,3... as input the same way GROUP BY does?

Hello all!

In the following lesson:
link

We see the group by can accept 1,2,3… and so on as columns to group by. With that, Having also works column-wise. Why is the following not valid?

-- VALID
SELECT price, 
   ROUND(AVG(downloads)),
   COUNT(*)
FROM fake_apps
GROUP BY 1
having count(*) > 10

-- INVALID
SELECT price, 
   ROUND(AVG(downloads)),
   COUNT(*)
FROM fake_apps
GROUP BY 1
having 3 > 10

*for some reason I can’t make a codebyte, sorry for the inconvenience

Thanks!

No. HAVING uses conditions that filters groups (WHERE filters rows). So, no, you cannot use the column numbers. It’s also not a great idea to use the numbers but rather use the col names instead.

1 Like

In the example I gave does COUNT(*) work as a group or a column?

It’s also not a great idea to use the numbers but rather use the col names instead.

I see. With that, it was a recommendation made by codecademy. If that is so, maybe a disclaimer is due (i.e. “You can, but it’s not common practice”)

COUNT(*) is an aggregate function that counts all rows including duplicates & nulls.

Yea, I know they suggested it (I took all SQL courses and parts of the POSTGRESql course).

Check out other resources (including official docs) too if you feel like the explanation doesn’t make sense to you. There’s always more than one way of explaining programming concepts.