frenciaj frenciaj - 3 months ago 6
Ruby Question

DB Query matching ids and sum data on columns

Here is the info i have on my tables, what i need is to create a report based on certain dates and make a sum of every stock movement of the same id

Table one Table Two
Items Stocks
---------- ---------------------------
ID - NAME items_id - altas - bajas - created_at

1 White 4 5 0 8/10/2016
2 Black 2 1 5 8/10/2016
3 Red 3 3 2 8/11/2016
4 Blue 4 1 4 8/11/2016
2 10 2 8/12/2016


So based on a customer choice of dates (on this case lets say it selects all the data available on the table), i need to group them by items_id and then SUM all altas, and all bajas for that items_id, having the following at the end:

items_id altas bajas
1 0 0
2 11 7
3 3 2
4 6 4


Any help solving this?

Answer

Hope this will help:

Stock.select("sum(altas) as altas, sum(bajas) as bajas").group("item_id")
Comments