PostgreSQL: How to multiply two columns and display result in same query?

Question

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   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 * qty it says

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 212 but It says 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 here but I'm wondering if there is an easier way with less code.

Answer 1

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 statement to temporary derived table (PostgreSQL: using a calculated column in the same query)

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

Answer 2

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 by in your query (I presumed because of aggregation) and in order to find sum(newprice*qty), you will need a sub select :

select sum(newprice*qty) from
(
    select Greatest(p.price,0) as newprice, sum(q.qty) as qty
    from ....
) T

Answer 3

Try this:

select sum(price*qty) as result
from yourtable;