Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2007
    Posts
    29

    Unanswered: Multiply column values

    Hi All,

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

    Column1
    1
    3
    4

    The output should be : 12

    Please help me.

    Thanks in advance
    Shiya

  2. #2
    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
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  3. #3
    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
  •