Can i use Column Referencing in HAVING clause?

SELECT category, count(*) 
    from startups
    GROUP BY 1
        HAVING   count(*) > 0;

The above code won’t work with column referencing in HAVING statement.Please help!

Can you provide a link to the exercise?

count(*)

returns the number of rows in the entire table, including null values.

@sohilkhan5514358373, welcome to the forums.

Can you please explain what it is exactly you want to do? Your provided code works just fine:

For the 11th & 12th subpart how can i use column reference in HAVING clause?
Thanks

I want to modify 4th line in your screenshot using column reference.
Thanks

As far as I know, you can’t. The HAVING clause works in conjunction with GROUP BY and is evaluated as a boolean expression. See the SQLite documentation:

If a HAVING clause is specified, it is evaluated once for each group of rows as a boolean expression. If the result of evaluating the HAVING clause is false, the group is discarded.

So, if my understanding is correct and you want to say…

--11 & 12
  SELECT category, COUNT(*) AS 'Total No. Of Companies' FROM startups
  GROUP BY 1
  HAVING 2 > 3 --instead of COUNT(*) > 3
  ORDER BY 2 DESC;

…then the HAVING clause will return FALSE for each group and the result won’t contain anything.

Referencing columns by the column index is really only used in GROUP BY and ORDER BY clauses.

1 Like