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.
| Author |
Topic |
|
motokevin
Starting Member
36 Posts |
 Posted - 2002-05-23 : 18:15:27
|
| SQL has an aggregate function SUM() that will sum the values of a column.Does anyone know if SQL has an aggregate function to get the product of all values in a column.In other words, where SUM might produce "1 + 2 + 8 + ... etc."I want to have it product "1 * 2 * 8 * ... etc."I'm sure I could figure out some other way to get the product that I am looking for, I was just hoping that SQL would have a built in function to do this for me. |
|
|
JustinBigelow
SQL Gigolo
1157 Posts |
Posted - 2002-05-23 : 18:38:05
|
| If you are running SQL Server 2000 then you could do it as a UDF, if you are on SQL 7.0 or below you would have to do it as a stored procedure.Justin |
 |
|
|
LarsG
Constraint Violating Yak Guru
284 Posts |
Posted - 2002-05-23 : 18:50:26
|
| declare @p intselect @p = coalesce(@p,1)*coalesce(c,1) from tselect @p |
|
|
|
motokevin
Starting Member
36 Posts |
Posted - 2002-05-23 : 19:32:04
|
| Thanks LarsThat works great.I'm surprised that SQL doesn't have this as a built in function. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-05-23 : 21:20:45
|
| You can fake it by using a little math. If you take the logarithm of two numbers, add the logs together, you get the logarithm of their product. You can then use the EXP() function to find the actual product:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10240 |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-05-24 : 02:51:02
|
| Hi Rob,That is the same technique presented in Celko's SQL for smarties. I noted in the other link you couldn't remember where you saw it first. He credits it to Roy Harvey on the Compuserve Forums.There is one missing piece though. What happens when there are negative numbers or a 0?<Pulling out a book to check> Okay, he had 3 products. I can't be bothered to check the syntax, so I'm about to butcher this, but it was roughly:SELECT (EXP(SUM(LN(NULLIF(ABS(c), 0))))) *(CASE WHEN MIN(ABS(c))= 0 THEN 0.0 ELSE 1.0 END) *(CASE WHEN (SUM(CASE WHEN SIGN(c) = -1 THEN 1 ELSE 0 END)%2)=1 THEN -1 ELSE 1 END)FROM YourTable----------------------"O Theos mou! Echo ten labrida en te mou kephale!" |
|
|
|
|
|
|
|
|
PRODUCT() Function