Sum of two columns

i have there tables

products

              id   name
              1     item1
               2    items2
               3    items3

stocks

           pid     qty
            2         300
            2          50
            1           20
            3            60

loadings

             pid     qty
              2         30
             1           20
             3            60

I need to run a query where I get the below result by today

            pid            Tqtystock                  TqtyLoading
              1                 20                                 20
              2                350                                 30   
              3                60                                     60

camilovietnam

6 years ago

Level 1

It looks like you want to do first a SUM(...) query to get the sum of 'qty' in the stocks table, something like SUM(qty) GROUP BY pid, then join this result to the products table, I'd say it would be something like

Product::leftJoin(
    DB::raw('(SELECT SUM(qty) FROM stocks GROUP BY pid) as stock_sum'),
    'stock_sum.pid', '=', 'products.id'
)

I'm not sure this would work, but the idea is you first sum the stocks table and group by ID, then join using this ID and the ID in the stocks table.

jlrdw

6 years ago

Level 75

Also look at https://www.mysqltutorial.org/mysql-sum/

Working the examples will give you a better idea.

Please or to participate in this conversation.