Pb to retreive max value with aggregate subquery

I would like to know which products are my best sells by sellers, but i
would like to retreive this info by product id, seller id and the total
amount of sells for this product.

My Sells table is :
Seller_idProduct_idTotaldate_s
1 2 1020/05/04
2 4 1512/05/04
3 5 2206/06/04
1 5 1807/06/04
4 8 1213/05/04
7 2 1119/05/04
3 4 1421/05/04
2 4 1418/05/04
1 5 1817/06/04
2 5 5008/05/04

etc...

I know how to retreive the total sells by product id and seller id

SELECT Seller_id, Product_id, SUM(Total) AS total
FROM Sells
WHERE date_s > '01/05/04'
GROUP BY Seller_id,Product_id order by Seller_id

Seller_idProduct_idTotal
1 5 36
1 2 10
2 5 50
2 4 29
3 5 22
3 4 14

I would like retreive only the max of total, and the Seller id and
product id, like this :

Seller_idProduct_idTotal
1 5 36
2 5 50
3 5 22

How can i do without using a temp table ?

Thanks for your help.Try this:

SELECT seller_id, product_id, SUM(total) AS total
FROM Sells AS S
WHERE date_s > '20040501'
GROUP BY seller_id, product_id
HAVING SUM(total) =
(SELECT MAX(total)
FROM
(SELECT SUM(total) AS total
FROM Sells
WHERE date_s > '20040501'
AND seller_id = S.seller_id
AND product_id = S.product_id) AS T) ;

--
David Portas
SQL Server MVP
--|||David Portas wrote:

> Try this:
> SELECT seller_id, product_id, SUM(total) AS total
> FROM Sells AS S
> WHERE date_s > '20040501'
> GROUP BY seller_id, product_id
> HAVING SUM(total) =
> (SELECT MAX(total)
> FROM
> (SELECT SUM(total) AS total
> FROM Sells
> WHERE date_s > '20040501'
> AND seller_id = S.seller_id
> AND product_id = S.product_id) AS T) ;

Thank but i think i need to add a GROUP by in the last select:

SELECT seller_id, product_id, SUM(total) AS total
FROM Sells AS S
WHERE date_s > '20040501'
GROUP BY seller_id, product_id
HAVING SUM(total) =
(SELECT MAX(total)
FROM
(SELECT SUM(total) AS total
FROM Sells
WHERE date_s > '20040501'
AND seller_id = S.seller_id
AND product_id = S.product_id GROUP BY seller_id, product_id )
AS T) ;

Thanks a lot|||Almost. I think you wanted the max for each Seller_ID so you just need
Seller_ID in that WHERE clause and Product_ID in the GROUP BY - which is
what I should have posted to start with.

SELECT seller_id, product_id, SUM(total) AS total
FROM Sells AS S
WHERE date_s > '20040501'
GROUP BY seller_id, product_id
HAVING SUM(total) =
(SELECT MAX(total)
FROM
(SELECT SUM(total) AS total
FROM Sells
WHERE date_s > '20040501'
AND seller_id = S.seller_id
GROUP BY product_id ) AS T) ;

--
David Portas
SQL Server MVP
--