Aggregate Function Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Aggregate Function Help

Author

Topic

Tracey
Starting Member

40 Posts

Posted - 2004-07-13 : 23:07:52

Hello Friends,
I need some help with the aggregate function.
Iam trying to find the product with most qty Sold.

Currently iam using this query which gives only the Max qty Value:

select max(Total)from
( select Product_Id,sum(qty)as Total
from
zzz
Group by Product_id) as A

1)I need the output with both ProductName and max(Qty)
2)The query should also return all Products and Qty value if more than one product has same max(qty) value

Any help much appreciated
thanks
Tracey

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-13 : 23:27:13

select productid, sum(qty) from xxx
group by productid
having sum(qty) in (SELECT TOP 1 SUM(Qty) FROM xxx GROUP BY ProductID ORDER BY Sum(qty) DESC)

You can then link this to the Product Name field (which i assume is in another table)..

HTH,

Tim

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 03:54:24

Timmy, am I missing something here, or can you just do
[CODE]
select TOP 1
productid, sum(qty)
from xxx
group by productid
ORDER BY SUM(qty)
[/CODE]
or, to get the Product Name, as you say, join to Product Table
[CODE]
SELECT ProductID,
ProductName,
TotalQty
FROM MyProductTable PT
JOIN
(
select TOP 1
productid,
[TotalQty] = sum(qty)
from xxx
group by productid
ORDER BY SUM(qty)
) X
ON X.ProductID = PT.ProductID
[/CODE]
Kristen

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-14 : 19:08:59

Kristen,

There was an extra requirement under Tracey's item (2) that requests that the query return multiple rows if several products had the same max(qty) value. That's what makes my solution look a bit messy.

Tim

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 21:04:47

Ah, failed at the "Read the Question" hurdle!

Would WITH TIES help?

Kristen

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-07-14 : 21:51:04

Yep - with ties would do it.

Learn something new every day.....

Kristen
Test

22859 Posts

Posted - 2004-07-14 : 22:00:38

OK, so I think we are at:


SELECT ProductID,
       ProductName,
       TotalQty
FROM   MyProductTable PT
       JOIN
       (
           select TOP 1 WITH TIES
                  productid, 
                  [TotalQty] = sum(qty) 
           from zzz
           group by productid
           ORDER BY SUM(qty)
       ) Z
           ON Z.ProductID = PT.ProductID

Kristen

Tracey
Starting Member

40 Posts

Posted - 2004-07-15 : 02:32:47

thank you guys!
Your help so much appreciated

Tracey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources