PostgreSQL 列的乘法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18966747/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL multiplication of columns
提问by bbbb
I have Postgres database and I have to multiply column by column like this:
我有 Postgres 数据库，我必须像这样逐列相乘：
SELECT SUM(column1*column2) AS res (...)
And the result is 0. Both columns are realtype.
But the multiplication operator works great when I do:
结果是0。两列都是real类型。
但是当我这样做时，乘法运算符效果很好：
SELECT SUM(column * 100) AS res (...)
Is it possible to do arithmetics using columns in PostgreSQL?
是否可以使用 PostgreSQL 中的列进行算术运算？
回答by Erwin Brandstetter
This query works just fine:
这个查询工作得很好：
SELECT SUM(column1 * column2) AS res
FROM tbl;
If your result resis 0, then you must have:
如果您的结果res是0，那么您必须：
0in one or both of the columns and none of themNULLin at least one row.0orNULLin one or both of the columns for every other selected row.
0在一列或两列中，并且NULL至少在一行中没有。0或NULL在每一个选定的行的一列或两列中。
Or there is some other trivial misunderstanding involved. Maybe you are confusing your column alias reswith result?
或者还有其他一些微不足道的误解。也许您将列别名res与result?
回答by jperelli
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.
还要注意 NULL 值。如果 column1 或 column2 中的某个值为 NULL，则 SUM() 的所有结果都可以为 NULL，并且根据您使用的语言和连接库，您可以看到 0 作为结果而不是 NULL。
