Postgres Multiplication between Columns and Sum of result

Question

I have a data table with the following:

Price | Date | Quantity 20 | 01-02 | 1 20 | 01-03 | 2 30 | 01-01 | 2 30 | 01-03 | 1 40 | 01-02 | 3

For each row, I would like to be able to multiple the value of the "Price" column with the same row's Quantity value, then sum those values, and return a single value to determine total revenue for the month.

Here is what I have, but I keep getting stymied:

select sum( Select ("Price"*"Quantity") as "Subtotal") as "Total" from Sales where "Date" between '01/01/2016' and '01/31/2016';

However, this doesn't appear to do the trick. Does somebody know how I might tweak it to get the result I want?

Giorgos Betsos · Accepted Answer · 2016-02-18 13:02:54Z

2

Try this:

select sum("Price"*"Quantity") as "Total"
from Sales
where "Date" between '2016-01-01' and '2016-01-31';

You can directly apply sum on the product of "Price", "Quantity" fields. The above query returns a single row.

Demo here

edited Feb 18, 2016 at 13:02

answered Feb 18, 2016 at 12:48

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

Collectives™ on Stack Overflow

Postgres Multiplication between Columns and Sum of result

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest