SQL Server:How to Use an Aggregate Function Like Max in a Where Clause

Sql Server : How to use an aggregate function like MAX in a WHERE clause

You could use a sub query...

WHERE t1.field3 = (SELECT MAX(st1.field3) FROM table1 AS st1)

But I would actually move this out of the where clause and into the join statement, as an AND for the ON clause.

Aggregate function in SQL WHERE-Clause

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 ;

SQL MAX in WHERE

Presumably you want:

select c.id, max(i.period_to)
from public.contract c left join
     public.invoice
     on i.contract_id = c.id
where c.close_type IS NULL and c.payment_type <> 'TRIAL' 
group by c.id
having max(i.period_to) <= '2017-10-01' OR max(i.id) IS NULL;

How to use aggregate function MAX with SELECT *?

I suppose you're on SQL Server as it's a common error.
If you want to aggregate, you need to group by explicitely each columnyou want to show.

In your case the query should be like

select *, max(sequence)
from <your table>
group by <an explicit list of all columns without seqence>
order by max(sequence)

CASE WHEN condition with MAX() function

You can use dense_rank() to filter the last 2 dates in your table. After that you can use either conditional case expression with sum() to calculate the required value

select [date],
       sum_act  = sum(case when rn = 1 then [debet] else 0 end),
       sum_prev = sum(case when rn = 2 then [debet] else 0 end),
       diff     = sum(case when rn = 1 then [debet] else 0 end)
                - sum(case when rn = 2 then [debet] else 0 end)
from
(
    select *, rn = dense_rank() over (order by [date] desc)
    from   tbl 
) t
where rn <= 2
group by [date]

db<>fiddle demo

SQL: select max(A), B but don't want to group by or aggregate B

Expanding this to work for any number of houses (rather than only working for exactly one house)...

SELECT
  house.*,
  room.*
FROM
  house
OUTER APPLY
(
  SELECT TOP (1) room.create_date, room.color
    FROM room
   WHERE house.house_id = room.house_id
ORDER BY room.create_date DESC
)
  AS room

SUM UP two columns and then find the find MAX value in SQL Server

Try this - just sort by the Total column in a descending fashion, and take the first row in the result:

SELECT TOP (1)
    Q1.E_ID, Q1.E_Name, Q1.Total
FROM 
    (SELECT  
         E_ID, E_Name, ISNULL(T1, 0) + ISNULL(T2, 0) AS Total
     FROM    
         table1) AS Q1
ORDER BY
    Q1.Total DESC;

---

Related Topics