CODEWITHSUNDEEP
laravel
Rahul W
Rahul W

Reputation: 195

Get Total amount & Quantity for same ID

Good Afternoon... I wanted to get the data from database which will be groupby customerID and base on same data in below format :

customerID From date to Date Total Quantity Total Amount

Refer attached images for my database. I able to get data groupby customerID but stuck for futher details.

$test = Dairyincome::get()->groupBy('customerID')->toArray();
dump($test);

Expected result

customerID From date to Date Total Quantity Total Amount
Cust-01 2022-02-10 2022-02-11 (10+2.3)=12.30 (450+98.90)=548.90

same for other ID

Hope i explained my problem and thanks in Advanceenter image description here

Upvotes: 0

Views: 1393

Answers (1)

W Kristianto
W Kristianto

Reputation: 9303

I don't recommend using collection (you use ->groupBy() after ->get()).

You need to know how it can work using SQL first, before using Eloquent or the Query Builder.

I assume your table is dairyincomes :

SELECT
  customerID, 
  MIN(date) as "From date", 
  MAX(date) as "To Date",
  SUM(quantity) as "Total Quantity",
  SUM(amount) as "Total Amount"
FROM
  dairyincomes
GROUP BY
  customerID
  • The MIN() function returns the minimum value in a set of values.
  • The MAX() function returns the maximum value in a set of values.
  • The SUM() function is an aggregate function that allows you to calculate the sum of values in a set.

Eloquent :

Dairyincome::selectRaw('customerID, MIN(date) as "From date", MAX(date) as "To Date", SUM(quantity) as "Total Quantity", SUM(amount) as "Total Amount"')
    ->groupBy('customerID')
    ->get();

Also, you can use DB::raw() btw.

Reference :

Upvotes: 1

Related Questions