Using output of function from select in where clause

Question

What is the correct way to write an SQL Query so I can use the output of a function that I have used in the Select Statement in the Where clause?

Data Table:

ID    Count_ID
111   2
111   2
222   3
222   3
222   3
333   1

Query:

Select ID, Count(Table1.ID) As Count_ID
From Table1
Where Count_ID = 3
Group By ID

It gives me invalid column name currently in there Where Clause for Count_ID.

Which RDBMS? If MySQL, you're going to need a HAVING clause. — Corbin
– Corbin, Commented Jul 30, 2013 at 23:49
Is there a typo in between Table and 1? Other than that, you're query should run fine (sqlfiddle.com/#!2/6c98b/1)... What are your desired results? — sgeddes
– sgeddes, Commented Jul 30, 2013 at 23:49

Corbin · Accepted Answer · 2013-07-30 23:52:53Z

3

There's a circular dependency on your filtering. You want to only select records where the count is 3, but you must count them before you can determine this. This means that you need a HAVING clause rather than a WHERE clause (to filter on an aggregate function, you always need a HAVING clause).

Furthermore, you can't use an aliased column name for an aggregate function in a WHERE or HAVING clause. You have to repeat the function in the filtering:

Select ID, Count(ID) As Count_ID
From Table1
Group By ID
HAVING Count(ID) = 3;

answered Jul 30, 2013 at 23:52

Corbin

33.6k7 gold badges73 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Curt · Accepted Answer · 2013-07-30 23:52:38Z

2

In this case, because you're referencing an aggregate function and a grouping, you have to use a HAVING clause.

 Select ID, Count(Table1.ID) As Count_ID
   From Table1
        Group By ID
        Having Count(Table1.ID)  = 3

answered Jul 30, 2013 at 23:52

Curt

5,7511 gold badge25 silver badges36 bronze badges

1 Comment

Michael Carn-Bennett Over a year ago

Thanks but Corbin beat you to it but seconds by the looks of it.

nzs · Accepted Answer · 2013-07-31 00:27:22Z

1

Alternatively you can use this too:

SELECT ID, Count_ID
FROM
  (SELECT ID, Count(ID) AS Count_ID
   FROM Table1
   GROUP BY ID) Calced
WHERE Calced.Count_ID = 3;

http://sqlfiddle.com/#!4/f2a73/5

answered Jul 31, 2013 at 0:27

nzs

3,25219 silver badges22 bronze badges

1 Comment

Michael Carn-Bennett Over a year ago

This method also works, but going to use HAVING Instead. Thanks.

Collectives™ on Stack Overflow

Using output of function from select in where clause

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related