CODEWITHSUNDEEP
sqlpostgresql
John
John

Reputation: 1852

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

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.

Upvotes: 8

Views: 28194

Answers (3)

Jan 'splite' K.
Jan 'splite' K.

Reputation: 2114

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

Upvotes: 14

HelloNewWorld
HelloNewWorld

Reputation: 335

Try this:

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

Upvotes: 1

Deep
Deep

Reputation: 3202

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

Upvotes: 4

Related Questions