By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,759 Members | 1,727 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,759 IT Pros & Developers. It's quick & easy.

Pb to retreive max value with aggregate subquery

P: n/a
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,Product_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.

Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
--
Jul 20 '05 #2

P: n/a
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
Jul 20 '05 #3

P: n/a
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
--
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.