473,544 Members | 2,247 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Jul 20 '05 #1
3 2397
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
1241
by: pb648174 | last post by:
In the below structure, if I wanted to get the Id of the comment for each Generic record having the latest comment time, how would I do that not using a subquery? Table: Generic Id Description Table: Comment Id
2
3649
by: Greg Stark | last post by:
I find I often want to be able to do joins against views where the view are aggregates on a column that has an index. Ie, something like SELECT a.*, v.n FROM a JOIN (select a_id,count(*) as n group by a_id) as v USING (a_id) where there's an index on b.a_id. assume there are other tables being joined so I can't just move the aggregate to...
5
4895
by: margospencer | last post by:
Does db2 support this subquery: select b.time_ssno, sum(b.time_hours) as hours95, (select sum(a.time_hours) from p0230.pyrltime_time_v1 a where time_date between '2005-01-01' and '2005-03-31' and time_orgn = '2300' and time_type = 'REG' and a.time_ssno = b.time_ssno) as total_hours from p0230.pyrltime_time_v1 b
5
6574
by: Rod | last post by:
I have a client site where the code below has been working happily for at least four months. The site is using SQL Server 7. The code is ASP.NET Last week an error appeared related to the following SQL statement. INSERT INTO OrderItems (ClientID, ProductID, OrderHeaderID, Quantity, Dispatched, BackOrdered) SELECT ClientID, ProductID,...
1
3139
by: Scott Gerhardt | last post by:
Hello, I am new to the list, my apology if this question is beyond the scope or charter of this list. My questions is: What is the best method to perform an aggregate query to calculate sum() values for each distinct wid as in the example below, but except for all wid's (not just WHERE wid='01/1-6-1-30w1/0'). Also, performance wise,...
3
6780
by: Goran Djuranovic | last post by:
Hi All, Does anyone know how to retreive deepest XPath value from XML document by using VB.NET? For example, if I had an XML file like this: <Root> <Customer> <Name>MyName</Name> </Customer> </Root> I would like to retreive "\Root\Customer\Name" out of it. Something like:
5
6087
by: BillCo | last post by:
I just wasted a long time figuring out this and I figure if I post it might save someone some pain! Jet (DAO) will allow you to to use nested aggregate functions like building blocks, e.g.: SELECT A, sum(B) as Answer1, Answer1 * 2 as DoubleAnswer
2
7175
by: eighthman11 | last post by:
Hi everyone. I am updating a table with aggregate results for multiple columns. Below is an example of how I approached this. It works fine but is pretty slow. Anyone have an idea how to increase performance. Thanks for any help. UPDATE #MyTable SET HireDate=(Select Min(Case When Code = 'OHDATE' then DateChanged else null end) From...
6
2218
by: Kelii | last post by:
Hello, So I have a form which shows all items available for sale, when it was last sold, where it was last sold, and whether it is active or inactive. I would like to be able to edit the active/inactive field (its a yes/ no or boolean type field); however, I am not able to make these edits because of the max aggregate function used on...
0
7437
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7373
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7781
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7389
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
1
5306
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4930
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3427
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3421
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1848
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.