Aggregate function in SQL WHERE-Clause

Question

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

User · Answer

SELECT COUNT         FROM agents    HAVING COUNT    gt 3      See more below link    http   www w3resource com sql aggregate-functions count-having php sthash 90csRM4I dpuf  1  call me if any query 85110 51548 http   www w3resource com sql aggregate-functions count-having php

User · Answer

UPDATED query    select id from t where id  lt   select max id  from t     It ll select all but the last row from the table t

User · Answer

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

User · Answer

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   gt  20000   Or  select EmployeeId  sum amount  from Sales group by Employee where EmployeeId in       select max EmployeeId  from Employees

User · Answer

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    gt   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    gt   5                   AS cnt FROM orders o GROUP BY o customerid

User · Answer

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    gt  1000 GROUP BY Product   References   1  2   Hope helps someone

[sql] Aggregate function in SQL WHERE-Clause

Examples related to sql