How to get all the sum of all the distinct items in MySQL in a single query

Question

How do I get the sum of all the distinct items? e.g. 0001 equals 10 and 0002 equals 10.

What could be the best possible (MySQL or Laravel Query Builder) query on the sample data provided below.

tbl_transactions :

+-------+-----------+-----------+
| id    | item_code | quantity  |
+-------+-----------+-----------+
| 1     | 0001      | 6         |
| 2     | 0001      | 4         |
| 3     | 0002      | 7         |
| 4     | 0002      | 3         |
+-------+-----------+-----------+

Answer 1

For MySQL, a basic GROUP BY query should work here:

SELECT item_code, SUM(quantity) AS total
FROM tbl_transactions
GROUP BY item_code;

Laravel code:

$report = DB::table('tbl_transactions')
    ->selectRaw('item_code, SUM(quantity) AS total')
    ->groupBy('item_code')
    ->get();

Answer 2

Try this mysql

select item_code, sum(quantity) as sum
from tbl_transactions
group by item_code;

OR

DB::table('tbl_transactions')
            ->distinct('item_code')
            ->count('quantity');

Answer 3

you can try this !!!

$report = DB::table('tbl_transactions')
->select([DB::raw("SUM(quantity) as sum"),'item_code']) 
->groupBy('item_code')
->get();