I have 2 tables: invoices, and coletes. When I create the view page for the invoice I want all the coletes to be numbered in there and their totaleuro summed
basically it should look something like this:
invoiceID coleteID TotalEuro(Of Colete)
1 1 100
2 200
3 400
Total 700
2 4 200
5 300
6 500
Total 1000
My query looks like this
$sum = DB::table('coletes')
->join('invoices','coletes.invoice_id','=','invoices.id')
->select(DB::raw('
SUM(coletes.totaleuro) as totaleuro',
))
->get();
But instead of returning invoice1 total : 700 invoice2 total: 1000
I just get total: 1700
Anybody has any idea how to solve this query?
In your query, you are considering all the coletes belong to a single invoice. you need to use group by to get invoice level total
$sum = DB::table('coletes')
->join('invoices','coletes.invoice_id','=','invoices.id')
->select(DB::raw('
SUM(coletes.totaleuro) as totaleuro',
))->groupBy('coletes.invoice_id')
->get();
so it will return two rows as per your example
MySQL Query for your example
CREATE TABLE coletes (
id INTEGER PRIMARY KEY,
invoice_number INTEGER NOT NULL,
product_id INTEGER not NULL,
amount INTEGER not null
);
-- insert
INSERT INTO coletes VALUES (0001, 1,1, 100);
INSERT INTO coletes VALUES (0002, 1,2, 200);
INSERT INTO coletes VALUES (0003, 1,3, 400);
INSERT INTO coletes VALUES (0004, 2,4, 200);
INSERT INTO coletes VALUES (0005, 2,5, 300);
INSERT INTO coletes VALUES (0006, 2,6, 500);
-- fetch
SELECT invoice_number, sum(amount) FROM coletes group by
invoice_number;
Output:
invoice_number sum(amount)
1 700
2 1000