1. Registered User
Join Date
Mar 2007
Posts
29

Hi All,

Is it possible to multiply the column values in a single select statement?

Column1
1
3
4

The output should be : 12

Shiya

2. Registered User
Join Date
Aug 2004
Location
France
Posts
754
Not directly, but with a little mathematics :
Code:
```rbaraer@Ora10g> DROP TABLE Table1;

Table dropped.

rbaraer@Ora10g> CREATE TABLE Table1
AS
(
SELECT 1 Column1 FROM DUAL
UNION ALL
SELECT 3 Column1 FROM DUAL
UNION ALL
SELECT 4 Column1 FROM DUAL
);  2    3    4    5    6    7    8    9

Table created.

rbaraer@Ora10g> SELECT EXP(SUM(LN(Column1)))
FROM Table1;  2

EXP(SUM(LN(COLUMN1)))
---------------------
12

rbaraer@Ora10g>```
Beware : this solution only works with numbers > 0, so we have to complicate it a little in order to handle numbers <= 0.

Code:
```SELECT POWER(-1, SUM(CASE ABS(Column1) WHEN Column1 THEN 0 ELSE 1 END))
*EXP(SUM(LN(ABS(CASE Column1 WHEN 0 THEN 1 ELSE Column1 END))))
*CASE SUM(CASE Column1 WHEN 0 THEN 1 ELSE 0 END) WHEN 0 THEN 1 ELSE 0 END Result
FROM Table1;```
Code:
```rbaraer@Ora10g> DROP TABLE Table1;

Table dropped.

rbaraer@Ora10g> CREATE TABLE Table1
AS
(
SELECT 0 Column1 FROM DUAL
UNION ALL
SELECT 3 Column1 FROM DUAL
UNION ALL
SELECT 4 Column1 FROM DUAL
);  2    3    4    5    6    7    8    9

Table created.

rbaraer@Ora10g> SELECT POWER(-1, SUM(CASE ABS(Column1) WHEN Column1 THEN 0 ELSE 1 END))
*EXP(SUM(LN(ABS(CASE Column1 WHEN 0 THEN 1 ELSE Column1 END))))
*CASE SUM(CASE Column1 WHEN 0 THEN 1 ELSE 0 END) WHEN 0 THEN 1 ELSE 0 END Result
FROM Table1;  2    3    4

RESULT
----------
0

rbaraer@Ora10g> DROP TABLE Table1;

Table dropped.

rbaraer@Ora10g> CREATE TABLE Table1
AS
(
SELECT -1 Column1 FROM DUAL
UNION ALL
SELECT 3 Column1 FROM DUAL
UNION ALL
SELECT 4 Column1 FROM DUAL
);  2    3    4    5    6    7    8    9

Table created.

rbaraer@Ora10g> SELECT POWER(-1, SUM(CASE ABS(Column1) WHEN Column1 THEN 0 ELSE 1 END))
*EXP(SUM(LN(ABS(CASE Column1 WHEN 0 THEN 1 ELSE Column1 END))))
*CASE SUM(CASE Column1 WHEN 0 THEN 1 ELSE 0 END) WHEN 0 THEN 1 ELSE 0 END Result
FROM Table1;  2    3    4

RESULT
----------
-12

rbaraer@Ora10g> DROP TABLE Table1;

Table dropped.

rbaraer@Ora10g> CREATE TABLE Table1
AS
(
SELECT 1 Column1 FROM DUAL
UNION ALL
SELECT 3 Column1 FROM DUAL
UNION ALL
2      SELECT 4 Column1 FROM DUAL
);  3    4    5    6    7    8    9

Table created.

rbaraer@Ora10g> SELECT POWER(-1, SUM(CASE ABS(Column1) WHEN Column1 THEN 0 ELSE 1 END))
*EXP(SUM(LN(ABS(CASE Column1 WHEN 0 THEN 1 ELSE Column1 END))))
*CASE SUM(CASE Column1 WHEN 0 THEN 1 ELSE 0 END) WHEN 0 THEN 1 ELSE 0 END Result
FROM Table1;  2    3    4

RESULT
----------
12

rbaraer@Ora10g>```
HTH & Regards,

rbaraer

3. Registered User
Join Date
Mar 2007
Posts
29
Hi RBARAER,

Thank you very much for the reply.

Let you know if I have any problem with it.

Regards,
Chella

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•