SELECT PRODUCT(Field)... - 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)

SELECT PRODUCT(Field)...

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-02-29 : 20:49:55

I have been searching ALL OVER THE WEB for an answer I got to the following question but I cannot seem to find it. I have been looking for over an hour :( Well, here goes:

I have some fields in my database that I need multiplied together:

UserID | Price | Volume
-----------------------
  1    |   2   |  200
  1    |   3   |  500
  1    |   2   |  100
-----------------------
This sql doesn't make any sense but I need something 
close to this if possible:

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

And the result should be:

UserID | Price | Volume
-----------------------
  1    |   12  |  800

Is this possible? Anybody knows how to do it?

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-02-29 : 20:54:36

SELECT UserID, EXP(Sum(LOG(Price))), SUM(Volume)
FROM table
GROUP BY UserID

You will probably have to ROUND off the result, as the LOG function generates a lot of decimal places.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-02-29 : 20:58:18

Excellent...thanx alot buddy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources