Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

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.

WHERE determines the set of row that grouping & aggregation is done on, so how can you use the aggregation to determine the rows? What is your reasoning, with reference to authoritative documentation? Otherwise you're just aking for us to write yet another presentation of the language with no details of what you misunderstand or do or don't understand. How are you stuck finding or understanding any reasonable presentation of grouping/aggregation? Anyway asking for off-site resources is off-topic. philipxy Jun 8, 2022 at 15:19

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
select EmployeeId, sum(amount)
from Sales
group by Employee
where EmployeeId in (
    select max(EmployeeId) from Employees)
                yes, that it is possible with having i do know. But the WHERE was given. I think it's a definition thing.
– n3on
                Jun 11, 2011 at 23:59
                thanks, I was going to leave my query in assignment and then i find this one...your answer really help me
– A.s. Bhullar
                Nov 29, 2013 at 18:00
                Thanks, this saved me a lot of time, all of the other sources out there didn't give the simplicity you did. Perfect answer.
– Jeff
                Apr 26, 2015 at 23:57
                what has to do [select max(EmployeeId) from Employees] that is in the second example with the first example [having sum(amount) > 20000].
– user3944364
                Jun 26, 2021 at 13:57

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 ;
                I think this possibility was added in the SQL-92 specs. No idea when various products added the functionality. Testing only with MySQL and SQL-Server shows slighly different behaviour (SQL-Server being more strict and probably more close to the specs). It would be interesting, if anyone else could check for other SQL implementations.
– ypercubeᵀᴹ
                Jun 12, 2011 at 10:24
                While purists will disagree, most places where I have worked say SQL to mean Microsoft SSMS, using TSQL
– JosephDoggie
                Oct 30, 2019 at 17:36

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.

I know it is possible as subquery, but i'm not sure if then i can say i can use an aggregat function in the WHERE... i thinks it's a definition thing. – n3on Jun 12, 2011 at 0:01 @n3on: I agree...I would argue that it is not possible to use aggregates directly in a WHERE clause - as I said. It is only possible to use them as part of a sub-query - and that would not count as 'in a WHERE clause' in my book. If you give the caveated, nuanced answer, I don't see how they can fault you. If it is a b****y multi-choice question, then you're more nearly stuck. – Jonathan Leffler Jun 12, 2011 at 2:17 I believe this will depend on the DBMS as Tim mentioned. In standard SQL, you'll have to write it as SELECT id FROM t WHERE id < (SELECT MAX(id) FROM t) – Coding District Jun 11, 2011 at 23:46 Yeah, you're right. I just remembered that it was possible from my early database tutorials where we were required to select top 5 rows without using TOP or LIMIT or ROWNUM. – Chandranshu Jun 11, 2011 at 23:49 Yes but then the aggregat function is in the select clause from the subquery and not in the WHERE. And I think a subquery can't be seen as an aggregat function. – n3on Jun 11, 2011 at 23:52

See more below link:

  • http://www.w3resource.com/sql/aggregate-functions/count-having.php#sthash.90csRM4I.dpuf]

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

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

    Create Your Function:

    CREATE FUNCTION getTotalSalesByProduct(@ProductName VARCHAR(500))
    RETURNS INT
    BEGIN
    DECLARE @TotalAmount INT
    SET @TotalAmount = (select SUM(SaleAmount) FROM Sales where Product=@ProductName)
    RETURN @TotalAmount
    

    Use Function in Where Clause

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

    References:

    Hope helps someone.

    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)

  • The inner query is used to fetch results that need to be filtered.
  • 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.
  • Finally, the filter can be applied to the aliased name of the column in the inner query
  • Welcome to SO! Please don't post code-only answers but add a little textual explanation about how and why your approach works and what makes it different from the other answers given. You may also have a look at our "How to write a good answer" entry. – ahuemmer Jul 29, 2022 at 10:01

    Thanks for contributing an answer to Stack Overflow!

    • Please be sure to answer the question. Provide details and share your research!

    But avoid

    • Asking for help, clarification, or responding to other answers.
    • Making statements based on opinion; back them up with references or personal experience.

    To learn more, see our tips on writing great answers.

  •