SQL Query Help

22 Oct 2005 11:46 PM

B. Mark McKinney

Using the following as sample data:

Create Table #Work
( GroupId int, ProductId int, Qty int)

INSERT #Work VALUES(6, 1, 25)
INSERT #Work VALUES(6, 1, 40)
INSERT #Work VALUES(6, 1, 50)
INSERT #Work VALUES(7, 2, 10)
INSERT #Work VALUES(7, 2, 20)
INSERT #Work VALUES(7, 2, 30)
INSERT #Work VALUES(7, 3, 30)
INSERT #Work VALUES(7, 3, 20)
INSERT #Work VALUES(7, 3, 35)
INSERT #Work VALUES(8, 4, 35)
INSERT #Work VALUES(8, 4, 40)
INSERT #Work VALUES(8, 4, 50)
INSERT #Work VALUES(9, 5, 35)
INSERT #Work VALUES(9, 5, 50)

What I would like to do is get the GroupId and the Sum of the Qty where the
sum is the largest. If there is a tie then I would like either GroupId.

SELECT GroupId, Sum(Qty) AS Total
FROM #Work
GROUP BY GroupId

results in the following:

GroupId     Total
----------- -----------
6           115
7           145
8           125
9           85

Thus for this data I would like 7 and 145 as the result.

This should be easy but the following does not work:

SELECT @Group = GroupId,
    @Total = Max(d.Total)
FROM (SELECT GroupId, Sum(Qty) AS Total
        FROM #Work
        GROUP BY GroupId) As d
GROUP BY GroupId

Mark

22 Oct 2005 11:51 PM

Tom Moreau

Thanx for posting DDL. Try:

select top 1
GroupID
, sum (Qty) Qty
from
#Work
group by
GroupID
order by
Qty desc

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..

"B. Mark McKinney" <BMarkMcKin***@discussions.microsoft.com> wrote in
message news:B0ACE95D-2DB6-4E48-ADB6-05AAD19F05EA@microsoft.com...

Using the following as sample data:

Create Table #Work
( GroupId int, ProductId int, Qty int)

INSERT #Work VALUES(6, 1, 25)
INSERT #Work VALUES(6, 1, 40)
INSERT #Work VALUES(6, 1, 50)
INSERT #Work VALUES(7, 2, 10)
INSERT #Work VALUES(7, 2, 20)
INSERT #Work VALUES(7, 2, 30)
INSERT #Work VALUES(7, 3, 30)
INSERT #Work VALUES(7, 3, 20)
INSERT #Work VALUES(7, 3, 35)
INSERT #Work VALUES(8, 4, 35)
INSERT #Work VALUES(8, 4, 40)
INSERT #Work VALUES(8, 4, 50)
INSERT #Work VALUES(9, 5, 35)
INSERT #Work VALUES(9, 5, 50)

What I would like to do is get the GroupId and the Sum of the Qty where the
sum is the largest. If there is a tie then I would like either GroupId.

SELECT GroupId, Sum(Qty) AS Total
FROM #Work
GROUP BY GroupId

results in the following:

GroupId     Total
----------- -----------
6           115
7           145
8           125
9           85

Thus for this data I would like 7 and 145 as the result.

This should be easy but the following does not work:

SELECT @Group = GroupId,
@Total = Max(d.Total)
FROM (SELECT GroupId, Sum(Qty) AS Total
        FROM #Work
        GROUP BY GroupId) As d
GROUP BY GroupId

Mark