Aggregate in Where clause
Hello, how can I do something like this:
Select contractitemnumber, itemnmbr, prclevel from bbtn..ct_itemcontractclin
where count(contractitemnumber) > 1
It thows this error:
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
Select contractitemnumber, itemnmbr, prclevel from bbtn..ct_itemcontractclin
where count(contractitemnumber) > 1
It thows this error:
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
almost...
Select contractitemnumber, itemnmbr, prclevel from bbtn..ct_itemcontractclin group by contractitemnumber, itemnmbr, prclevel
having count(contractitemnumber) > 1
Select contractitemnumber, itemnmbr, prclevel from bbtn..ct_itemcontractclin group by contractitemnumber, itemnmbr, prclevel
having count(contractitemnumber) > 1
ASKER
Sharath, it throws this:
Column 'bbtn..ct_itemcontractclin
Column 'bbtn..ct_itemcontractclin
You used count function. that means you are grouping the records in your table. So you have to include GROUP BY clause.
After that you want only the records having count(contractitemnumber) > 1. To include this filter, you should use HAVING clause as you are applying the filter after GROUPing records.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
check my first comment....
forgot to include COUNT
Open in new window