CODEWITHSUNDEEP
sql
n3on
n3on

Reputation: 2090

Aggregate function in SQL WHERE-Clause

In a test at university there was a question; is it possible to use an aggregate function in the SQL WHERE clause.

I always thought this isn't possible and I also can't find any example how it would be possible. But my answer was marked false and now I want to know in which cases it is possible to use an aggregate function in the WHERE. Also if it isn't possible it would be nice to get a link to the specification where it is described.

Upvotes: 98

Views: 334040

Answers (9)

Aachal Singh
Aachal Singh

Reputation: 1

You cannot directly use Aggregate function in WHERE clause but can use it in sub query .

Example :

SELECT * FROM GENDER
WHERE Salary in 
(SELECT min(Salary) FROM GENDER)   // this 
 will work

SELECT * FROM GENDER
WHERE Salary = min(Salary)      // this 
code is wrong

Upvotes: 0

FARIDUL islam
FARIDUL islam

Reputation: 1

Try this one

select SUM(RecQty) RecQty,ItemCode from 
CostLedger group by ItemCode
having sum(RecQty) > 2000

Upvotes: 0

Samir Lakhani
Samir Lakhani

Reputation: 743

SELECT COUNT( * )   
FROM agents   
HAVING COUNT(*)>3;

See more below link:

Upvotes: 10

RTZ
RTZ

Reputation: 11

If you are using an aggregate function in a where clause then it means you want to filter data on the basis of that aggregation function. In my case, it's SUM(). I'll jump to the solution.

(select * from(select sum(appqty)summ,oprcod from pckwrk_view group by oprcod)AS asd where summ>500)

  1. The inner query is used to fetch results that need to be filtered.
  2. The aggregate function which has to filter out must be given an ALIAS name because the actual name of the column inside an aggregate function is not accessible or recognized by the outer query.
  3. Finally, the filter can be applied to the aliased name of the column in the inner query

Upvotes: 1

Shaiju T
Shaiju T

Reputation: 6607

Another solution is to Move the aggregate fuction to Scalar User Defined Function

Create Your Function:

CREATE FUNCTION getTotalSalesByProduct(@ProductName VARCHAR(500))
RETURNS INT
AS
BEGIN

DECLARE @TotalAmount INT

SET @TotalAmount = (select SUM(SaleAmount) FROM Sales where Product=@ProductName)

RETURN @TotalAmount

END

Use Function in Where Clause

SELECT ProductName, SUM(SaleAmount) AS TotalSales
FROM Sales
WHERE dbo.getTotalSalesByProduct(ProductName)  > 1000
GROUP BY Product

References:

1. 2.

Hope helps someone.

Upvotes: 4

Chandranshu
Chandranshu

Reputation: 3669

UPDATED query: 

select id from t where id < (select max(id) from t);

It'll select all but the last row from the table t.

Upvotes: 11

Tim Schmelter
Tim Schmelter

Reputation: 460350

You haven't mentioned the DBMS. Assuming you are using MS SQL-Server, I've found a T-SQL Error message that is self-explanatory:

"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"

http://www.sql-server-performance.com/

And an example that it is possible in a subquery.

Show all customers and smallest order for those who have 5 or more orders (and NULL for others):

SELECT a.lastname
     , a.firstname
     , ( SELECT MIN( o.amount )
         FROM orders o
         WHERE a.customerid = o.customerid
           AND COUNT( a.customerid ) >= 5
        )
        AS smallestOrderAmount
FROM account a
GROUP BY a.customerid
       , a.lastname
       , a.firstname ;

UPDATE.

The above runs in both SQL-Server and MySQL but it doesn't return the result I expected. The next one is more close. I guess it has to do with that the field customerid, GROUPed BY and used in the query-subquery join is in the first case PRIMARY KEY of the outer table and in the second case it's not.

Show all customer ids and number of orders for those who have 5 or more orders (and NULL for others):

SELECT o.customerid
     , ( SELECT COUNT( o.customerid )
         FROM account a
         WHERE a.customerid = o.customerid
           AND COUNT( o.customerid ) >= 5
        )
        AS cnt
FROM orders o
GROUP BY o.customerid ;

Upvotes: 37

Jonathan Leffler
Jonathan Leffler

Reputation: 755064

You can't use an aggregate directly in a WHERE clause; that's what HAVING clauses are for.

You can use a sub-query which contains an aggregate in the WHERE clause.

Upvotes: 15

Jason Goemaat
Jason Goemaat

Reputation: 29234

HAVING is like WHERE with aggregate functions, or you could use a subquery.

select EmployeeId, sum(amount)
from Sales
group by Employee
having sum(amount) > 20000

Or

select EmployeeId, sum(amount)
from Sales
group by Employee
where EmployeeId in (
    select max(EmployeeId) from Employees)

Upvotes: 138

Related Questions