PostgreSQL：如何将两列相乘并在同一个查询中显示结果？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30909020/
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: How to multiply two columns and display result in same query?
提问by John
I have a query that it's select statement is this:
我有一个查询，它的选择语句是这样的：
select Greatest(p.price,0) as newprice, sum(q.qty) as qty
from ....
it gives me:
它给了我：
newprice qty
10 1
0 1
100 2
1 2
I want to multiply newprice with qty to get:
我想将 newprice 与 qty 相乘得到：
newprice qty result
10 1 10
0 1 0
100 2 200
1 2 2
When I try to do select Greatest(p.price,0) as newprice, sum(q.qty) as qty, newprice * qtyit says
当我尝试这样做select Greatest(p.price,0) as newprice, sum(q.qty) as qty, newprice * qty时说
ERROR: column "newprice" does not exist
I don't really need this extra column.
我真的不需要这个额外的专栏。
what i really want is : SUM(Greatest(p.price,0) * SUM(q.qty))which should give the value 212but It says ERROR: aggregate function calls cannot be nested
我真正想要的是：SUM(Greatest(p.price,0) * SUM(q.qty))它应该给出价值，212但它说ERROR: aggregate function calls cannot be nested
Basically all I need is to multiply two columns and sum the result. I know I can use CTE something similar to what is shown herebut I'm wondering if there is an easier way with less code.
基本上我需要的只是将两列相乘并对结果求和。我知道我可以使用类似于此处显示的内容的 CTE ，但我想知道是否有更简单的方法和更少的代码。
回答by Jan 'splite' K.
You can just repeat what you wrote:
你可以重复你写的内容：
select Greatest(p.price,0) as newprice,
sum(q.qty) as qty,
Greatest(p.price,0) * sum(q.qty) as result
from ...
or, you can wrap your select statementto temporary derived table(PostgreSQL: using a calculated column in the same query)
或者，您可以将select 语句包装到临时派生表（PostgreSQL：在同一查询中使用计算列）
select tmp.newprice,
tmp.qty,
tmp.newprice * tmp.qty as result
from (
select Greatest(p.price,0) as newprice,
sum(q.qty) as qty
from ...
) as tmp
回答by Deepak Pawar
Query should be like this :
查询应该是这样的：
select *, newprice*qty from
(
select Greatest(p.price,0) as newprice, sum(q.qty) as qty
from ....
) T
OR
或者
select Greatest(p.price,0) as newprice, sum(q.qty) as qty, Greatest(p.price,0)*sum(q.qty) as result
from ....
UPDATE :
更新 ：
you are using group byin your query (I presumed because of aggregation) and in order to find sum(newprice*qty), you will need a sub select :
您group by在查询中使用（我推测是因为聚合）并且为了找到 sum(newprice*qty)，您需要一个 sub select ：
select sum(newprice*qty) from
(
select Greatest(p.price,0) as newprice, sum(q.qty) as qty
from ....
) T
回答by HelloNewWorld
Try this:
试试这个：
select sum(price*qty) as result
from yourtable;
