Maximum of Sum - 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

General SQL Server Forums

New to SQL Server Programming

Maximum of Sum

Author

Topic

doobybug
Starting Member

4 Posts

Posted - 2009-02-23 : 03:42:22

Hi,

Another quick question! Guess I'm tired! I need to find the maximum of a calculated sum.

I have tblSupplies
SNO (FK)
PCODE (FK)
qty

I need to find the sum of the qty of each PCODE and then extract the maximum. How can I do that? Till now I wrote this to have the sum of each PCODE qty:

SELECT s.PCODE, SUM(s.qty) AS Total_Qty
FROM tblSupplies s
GROUP BY s.PCODE

krij
Starting Member

9 Posts

Posted - 2009-02-23 : 03:46:44

Pls. try with below query,

SELECT MAX(a.Total_Qty) as 'Max_Qty'
From
(
SELECT s.PCODE, SUM(s.qty) AS Total_Qty
FROM tblSupplies s
GROUP BY s.PCODE
) as a

doobybug
Starting Member

4 Posts

Posted - 2009-02-23 : 03:53:37

How can I get the PCODE as well along with the maximum? Ie: The PCODEs with the MAX value?

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-02-23 : 04:15:39

similar to this

declare @tblSupplies table (SNO int,PCODE varchar(100),qty int)

insert into @tblSupplies values (1,'a',10)
insert into @tblSupplies values (2,'a',20)
insert into @tblSupplies values (3,'b',30)
insert into @tblSupplies values (4,'B',10)
insert into @tblSupplies values (5,'C',10)
insert into @tblSupplies values (6,'c',20)
insert into @tblSupplies values (7,'d',10)

SELECT s.PCODE,sum(s.qty) AS Total_Qty
FROM @tblSupplies s GROUP BY s.PCODE
having sum(s.qty) =(select max(Total_Qty) from
(SELECT s.PCODE,sum(s.qty) AS Total_Qty
FROM @tblSupplies s
GROUP BY s.PCODE ) as a )

Karthik

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-23 : 04:51:34

[code]SELECT PCODE,
MAX(qty)
FROM (
SELECT PCODE,
SUM(qty) AS qty
FROM tblSupplies
GROUP BY PCODE
) AS a
GROUP BY PCODE[/code]

E 12°55'05.63"
N 56°04'39.26"

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-02-23 : 05:08:59

quote:
Originally posted by Peso

SELECT PCODE,
MAX(qty)
FROM (
SELECT PCODE,
SUM(qty) AS qty
FROM tblSupplies
GROUP BY PCODE
) AS a

E 12°55'05.63"
N 56°04'39.26"

I am getting this error. with above one

Msg 8120, Level 16, State 1, Line 12
Column 'a.PCODE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Karthik

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-23 : 06:09:08

[code]SELECT PCODE,TOTALSUM
FROM
(SELECT PCODE,ROW_NUMBER()OVER(ORDER BY TOTALSUM DESC)AS ROWID,TOTALSUM
FROM
(SELECT PCODE,SUM(QTY) OVER (PARTITION BY PCODE)AS TOTALSUM
FROM TABLE)Z)M
WHERE M.ROWID =1[/code]

matty
Posting Yak Master

161 Posts

Posted - 2009-02-23 : 06:12:54

SELECT PCODE,Total_Qty AS Max_Qty
FROM
(

SELECT PCODE,SUM(qty) AS Total_Qty,ROW_NUMBER() OVER (ORDER BY SUM(qty) DESC) AS Rownum
FROM @tblSupplies
GROUP BY PCODE
)t
WHERE Rownum = 1

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-02-23 : 06:15:43

quote:
Originally posted by matty

SELECT PCODE,Total_Qty AS Max_Qty
FROM
(

SELECT PCODE,SUM(qty) AS Total_Qty,ROW_NUMBER() OVER (ORDER BY SUM(qty) DESC) AS Rownum
FROM @tblSupplies
GROUP BY PCODE
)t
WHERE Rownum = 1

If two values have max(qty) then this will fail.

Karthik

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-23 : 06:25:01

[code]SELECT S.PCODE,M.MAXTOTAL
FROM
(SELECT PCODE,SUM(QTY) TOTALSUM
FROM TABLE
GROUP BY PCODE)S INNER JOIN(SELECT MAX(TOTALSUM)AS MAXTOTAL
FROM(SELECT PCODE,SUM(QTY) TOTALSUM
FROM TABLE
GROUP BY PCODE)P)M
ON M.MAXTOTAL = S.TOTALSUM[/code]

matty
Posting Yak Master

161 Posts

Posted - 2009-02-23 : 06:29:23

If more than one PCODE has same max(qty) then change ROW_NUMBER() to DENSE_RANK()

quote:
Originally posted by karthik_padbanaban

quote:
Originally posted by matty

SELECT PCODE,Total_Qty AS Max_Qty
FROM
(

SELECT PCODE,SUM(qty) AS Total_Qty,DENSE_RANK() OVER (ORDER BY SUM(qty) DESC) AS Rownum
FROM @tblSupplies
GROUP BY PCODE
)t
WHERE Rownum = 1

If two values have max(qty) then this will fail.

Karthik

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-02-23 : 06:34:41

quote:
Originally posted by matty

If more than one PCODE has same max(qty) then change ROW_NUMBER() to DENSE_RANK()

quote:
Originally posted by karthik_padbanaban

quote:
Originally posted by matty

SELECT PCODE,Total_Qty AS Max_Qty
FROM
(

SELECT PCODE,SUM(qty) AS Total_Qty,DENSE_RANK() OVER (ORDER BY SUM(qty) DESC) AS Rownum
FROM @tblSupplies
GROUP BY PCODE
)t
WHERE Rownum = 1

If two values have max(qty) then this will fail.

Karthik

That woks fine in this case.

Karthik

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources