MAX and 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

SQL Server 2000 Forums

SQL Server Development (2000)

MAX and SUM

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-01 : 11:36:00

I need to do a select sort of like this:

SELECT UserID, MAX(Price), SUM(Volume)
FROM table
GROUP BY UserID

Problem is that MAX(Price) will return several values and I only want to sum the Volume that actually has the highest price. Table is like this:


UserID  | Price  | Volume
-------------------------
  1     |   4    |   100
  1     |   3.9  |   200
  1     |   4    |   150
-------------------------

The select above will give 1, 4, 450 as the result but i need 1, 4, 250...any pointers people?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-03-01 : 11:48:24

Just add a WHERE clause along the lines of

WHERE price IN (SELECT MAX(price) FROM table GROUP BY UserId)

Raymond

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-01 : 12:04:17

[code]

USE Northwind
GO

CREATE TABLE myTable99(UserId int, Price decimal(5,2),Volume int)
GO

INSERT INTO myTable99(UserId,Price,Volume)
SELECT 1, 4, 100 UNION ALL
SELECT 1, 3.9, 200 UNION ALL
SELECT 1, 4, 150 UNION ALL
SELECT 2, 4, 100 UNION ALL
SELECT 2, 3.9, 200 UNION ALL
SELECT 2, 7, 500
GO

SELECT o.UserId, MAX(o.Price), SUM(o.Volume)
FROM myTable99 o
INNER JOIN (SELECT UserId, MAX(Price) AS Max_Price
FROM myTable99
GROUP BY UserId) AS i
ON o.Price = i.MAX_Price
AND o.UserId = i.UserId
GROUP BY o.UserId
GO

DROP TABLE myTable99
GO

[/code]

Brett

8-)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-01 : 12:33:21

Whoa Brett! That is dangerously close to what I'm actually doing, however I managed to do it with Ray's advice and if I don't get any major objections I'm gonna stick to the IN instead of the derived tables (but it's all a part of a larger derived table). And FYI doing this just removed one single and one double-nested cursor from 3 of my procedures (yes, I know...cursors are for rookies) so this seems to be the best Monday in a looong time

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

nr
SQLTeam MVY

12543 Posts

Posted - 2004-03-01 : 12:38:21

If you want it per UserID then

select UserID, max(Price), SUM(Volume)
FROM table t1
where Price = (select max(t2.Price) from table t2 where t1.UserID = t2.UserID)
group by UserID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-01 : 12:40:22

Damn...

Nigel's is way more effecient...

Brett

8-)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-01 : 12:55:59

Thanx to all of you, this is a derived table and what I ended up with:

(SELECT UserID, DescID, WinnerAlt, MAX(Price) AS Price, SUM(Volume) AS Volume 
FROM table a
WHERE Volume > 0
  AND Multi = 1
  AND Active = 1
  AND Request IS NULL
  AND Price IN (SELECT MAX(Price) FROM table b 
    WHERE Active = 1 AND Multi = 1 AND Request IS NULL
    GROUP BY UserID, DescID, WinnerAlt, Request)
GROUP BY UserID, DescID, WinnerAlt, Request) AS dt1

Unsure of the two group by's are really necissary but it works...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-01 : 13:17:29

Try this


SELECT UserID, DescID, WinnerAlt, MAX(Price) AS Price, SUM(Volume) AS Volume 
  FROM table a
 WHERE Volume > 0
   AND Multi = 1
   AND Active = 1
   AND Request IS NULL
   AND Price = (SELECT MAX(Price) 
		   FROM table b 
    		  WHERE Active = 1 
		    AND Multi  = 1 
		    AND Request IS NULL)
GROUP BY UserID, DescID, WinnerAlt, Request

Brett

8-)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-01 : 13:27:39

Nope doesn't work for some reason...I have tried both as a stand-alone select and as a derived table and none of them work. Have to stick with both group bys's I belive...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-01 : 13:45:16

quote:
Originally posted by Lumbago

Nope doesn't work for some reason...I have tried both as a stand-alone select and as a derived table and none of them work. Have to stick with both group bys's I belive...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Well of course it doesn't...because I'm a scrub



SELECT UserID, DescID, WinnerAlt, MAX(Price) AS Price, SUM(Volume) AS Volume 
  FROM table a
 WHERE Volume > 0
   AND Multi = 1
   AND Active = 1
   AND Request IS NULL
   AND Price = (SELECT MAX(Price) 
		   FROM table b 
    		  WHERE Active = 1 
		    AND Multi  = 1 
		    AND Request IS NULL
		 AND a.UserId = b.UerId)
GROUP BY UserID, DescID, WinnerAlt, Request

Just a minor detail.....

Brett

8-)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-01 : 13:49:25

I that case I belive I have to add
...
AND a.UserID = b.UserID
AND a.DescID = b.DescID
AND a.WinnerAlt = b.Winneralt
AND a.Request = b.Request

and will this make any dfference?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-01 : 13:57:49

You might want to actually try this.

SELECT
a.UserID,
a.DescID,
a.WinnerAlt,
b.Price AS Price,
b.Volume AS Volume
FROM
table a
INNER JOIN (
SELECT UserID,DescID,WinnerAlt,Request,MAX(Price) AS Price, SUM(Volume) AS Volume
FROM table WHERE Active = 1 AND Multi = 1 AND Request IS NULL) b ON
a.UserID = b.UserID
AND a.DescID = b.DescID
AND a.WinnerAlt = b.WinnerAlt
AND a.Request = b.Request
WHERE
a.Volume > 0
AND a.Multi = 1
AND a.Active = 1
AND a.Request IS NULL

The reason you have to do the GROUP BY twice is because you are summarizing everything twice. Anytime you have a derived value(MAX,SUM,etc), you must group the columns you are summarizing.

The proc above should save that part of your execution plan and use the indexes and recordsets a tad more efficiently.

You could get rid of the repetition in your where clause also if the derived table join takes care of that for you. Don't know your data structure well enough to answer that.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-01 : 14:03:27

Thanx alot people, I will have to leave this one for tomorrow 'cause I just happened to get a million other things to do for tonight. I'll let you know how it works :)

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-03-02 : 11:15:14

I tried this now Derrick but I would still need a GROUP BY in this derived table

SELECT UserID,DescID,WinnerAlt,Request,MAX(Price) AS Price, SUM(Volume) AS Volume
FROM table WHERE Active = 1 AND Multi = 1 AND Request IS NULL

Will this be any better then??

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-02 : 13:37:31

Yeah. If that gives you what you need that's by far the simplest and best. I was an idiot for not including the group by.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources