CODEWITHSUNDEEP
sqlpostgresql
Gary
Gary

Reputation: 39

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

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!

Upvotes: 1

Views: 4675

Answers (1)

Arth
Arth

Reputation: 13110

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

Upvotes: 6

Related Questions