sql aggregate product

[Jean]

Hi,
I have to do a product on a column. I need performance so I use this
technique:

SELECT column_name1, POWER(10,SUM(LOG10(column_name2))) AS Product
FROM table_name
GROUP BY column_name1

the problem is that the log ( 0 ) is impossible...


anyone has already face that problem?

Thanks everybody!

 

[Tom Ellison]

Dear Jean:

When any row contains a 0, the product for that group will be zero. How
about:

SELECT column_name1,
Exp(SUM(Log(column_name2)))
AS Product
FROM table_name T
WHERE NOT EXISTS (
SELECT *
FROM table_name T1
WHERE T1.column_name1 = T.column_name1
AND T1.column_name2 = 0)
GROUP BY column_name1
UNION ALL
SELECT column_name1,
0 AS Product
FROM table_name T
WHERE EXISTS (
SELECT *
FROM table_name T1
WHERE T1.column_name1 = T.column_name1
AND T1.column_name2 = 0)
GROUP BY column_name1

Tom Ellison

 

[Jean]

Thanks, It's really apreciated.