use distinct with multiple columns in laravel

Question

my table structure is as below

date        seller  unit    price   total
05-06-17    abc     14      700     9800
05-06-17    pqr     12      600     7200
05-06-17    abc     10      520     5200
06-06-17    abc     10      600     6000
06-06-17    pqr     15      520     7800
06-06-17    pqr     16      520     8320

I need to fetch record like

1) 'date' = '05-06-2017', 'seller' = 'abc', 'total' = '15000'
2) 'date' = '05-06-2017', 'seller' = 'pqr', 'total' = '7200'
3) 'date' = '06-06-2017', 'seller' = 'abc', 'total' = '6000'
4) 'date' = '06-06-2017', 'seller' = 'pqr', 'total' = '16120'

I need data from database in date wise. seller abc has two entry in a table for date 05-06-2017 so I need total of that day for seller abc as well pqr same things for the second date for all seller

Answer 1

Whenever we have multiple rows with same data that needs to be merged together in final output, we use group by functionality of mySQL.

so in your case you can use laravel query builder to do it in this way.

 DB::table('orders')->select('date', 'seller', DB::raw('SUM(total) as total'))
    ->groupBy('seller')
    ->groupBy('date')
    ->get();

Explanation

DB::select('orders')->select('date', 'seller', DB::raw('SUM(total) as total')) will query our orders table in our database & then fetch our provided columns i.e. date, seller and sum of total column as total

->groupBy('seller')->groupBy('date') It will merge the multiple records of same seller in same date.

->get(); we are get getting our data from the query.

Answer 2

You could try

DB::table('orders')
    ->select('date', 'seller', DB::raw('SUM(total)'))
    ->groupBy('date')
    ->groupBy('seller')
    ->get();