Aggregate function in SQL WHERE-Clause

Question

In the test at the university a question arose; Is it possible to use an aggregate function in a SQL WHERE .

I always thought that this was impossible, and I also can not find any example of how this would be possible. But my answer was flagged as false, and now I want to know in what cases you can use the aggregate function in WHERE . Also, if this is not possible, it would be nice to get a link to the specification where it is described.

Answer 1

You did not mention the DBMS. Assuming you are using MS SQL-Server, I found a T-SQL error message that is self-explanatory:

"An aggregate may not appear in WHERE if it is not in the subquery contained in the HAVING clause or selection list, and the aggregated column is an external link

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

---

And an example of how this is possible in a subquery.

Show all customers and the 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 actions are performed both in SQL-Server and in MySQL, but do not return the expected result. The next one is closer. I assume that this is due to the fact that the customerid , GROUPed BY field is used in the query-subquery connection in the first case PRIMARY KEY of the external table, and in the second case it is not.

Show all customer IDs and the 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 ; 

Answer 2

HAVING is similar to WHERE with aggregate functions, or you can 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) 

Answer 3

You cannot use an aggregate directly in a WHERE clause; what for offers having.

You can use the subquery that contains the aggregate in the WHERE clause.

Answer 4

UPDATED request:

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

It will select everything except the last row from table t.

Answer 5

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

See link below:

• http://www.w3resource.com/sql/aggregate-functions/count-having.php#sthash.90csRM4I.dpuf†[1] call me if you have a request: 85110 51548

• http://www.w3resource.com/sql/aggregate-functions/count-having.php

Answer 6

Another solution is to move the aggregate function to a 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 section

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

Literature:

1.2 .

Hope helps someone.