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.

Jan 'splite' K. · Accepted Answer · 2017-12-18 17:14:05Z

14

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

edited Dec 18, 2017 at 17:14

answered Jun 18, 2015 at 7:31

Jan 'splite' K.

2,2343 gold badges34 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Deep · Accepted Answer · 2015-06-18 07:38:24Z

4

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

edited Jun 18, 2015 at 7:38

answered Jun 18, 2015 at 7:29

Deep

3,2021 gold badge15 silver badges21 bronze badges

1 Comment

John Over a year ago

I'm trying to avoid this... as I will have to wrap it with another select to get the final value (in my example 212)

HelloNewWorld · Accepted Answer · 2015-06-18 07:45:52Z

1

Try this:

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

answered Jun 18, 2015 at 7:45

HelloNewWorld

3353 silver badges11 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related