Pb to retreive max value with aggregate subquery

[Not4u]

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_id Product_id Total date_s
1 2 10 20/05/04
2 4 15 12/05/04
3 5 22 06/06/04
1 5 18 07/06/04
4 8 12 13/05/04
7 2 11 19/05/04
3 4 14 21/05/04
2 4 14 18/05/04
1 5 18 17/06/04
2 5 50 08/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,Produ ct_id order by Seller_id

Seller_id Product_id Total
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_id Product_id Total
1 5 36
2 5 50
3 5 22

How can i do without using a temp table ?

Thanks for your help.

[David Portas]

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
--

[Not4u]

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

[David Portas]

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
--