How to do sum of product of two columns in postgres and group by two columns?

Question

I have a table named "Inventory" that looks looks like this:

date, store_id, item_num, description, on_hand, on_order, sold, cost, price

I want to output something like this:

date | store_id | inv costs| total on_hand
2014-08-03 | 100 | $57456 | 5876
2014-08-03 | 101 | $76532 | 4565
2014-07-27 | 100 | $12353 | 5346
2014-07-27 | 101 | $65732 | 8768

by grouping with date AND only showing store ids of '100' and '101'. The inventory cost is the part I can't figure out - I want to find the cost of inventory for those stores in those dates therefore i want to sum up all the on_hand*costs. Any help would be appreciated, thanks!

Show us the expected output

neshkeev
– neshkeev

2014-08-07 14:45:41 +00:00
Commented Aug 7, 2014 at 14:45 — neshkeev
– neshkeev, Commented Aug 7, 2014 at 14:45

Arth · Accepted Answer · 2014-08-07 14:48:51Z

6

Looks like you were almost there:

  SELECT date,
         store_id,
         SUM(on_hand*cost) inv_costs,
         SUM(on_hand) total_on_hand
    FROM Inventory
   WHERE store_id IN (100,101)
GROUP BY date, 
         store_id

answered Aug 7, 2014 at 14:48

Arth

13.1k5 gold badges43 silver badges72 bronze badges

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

Collectives™ on Stack Overflow

How to do sum of product of two columns in postgres and group by two columns?

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

Related