PostgreSQL multiplication of columns

Question

I have Postgres database and I have to multiply column by column like this:

SELECT SUM(column1*column2) AS res (...)

And the result is 0. Both columns are real type.
But the multiplication operator works great when I do:

SELECT SUM(column * 100) AS res (...)

Is it possible to do arithmetics using columns in PostgreSQL?

Answer 1

This query works just fine:

SELECT SUM(column1 * column2) AS res
FROM   tbl;

If your result res is 0, then you must have:

• 0 in one or both of the columns and none of them NULL in at least one row.
• 0 or NULL in one or both of the columns for every other selected row.

Or there is some other trivial misunderstanding involved. Maybe you are confusing your column alias res with result?

Answer 2

Try using a subquery

SELECT
  SUM(subq.multiplied_column)
FROM (
  SELECT
    column1 * column2 AS multiplied_column
  FROM
    table
) as subq

Also Take care of NULL values. If some value from column1 or column2 is NULL then all the result of SUM() can be NULL, and depending on the language you are using and the connection library, you can see 0 as a result instead of NULL.