get all data with SUM of Today and Sum of yesterday in same query

What have you done so far?

@TRAY2 - this one

            $sales = DB::table('Sales')->select(DB::raw('*'),DB::raw('sum(amount) as totalAmount'))
              ->whereRaw('Date(created_at) = CURDATE()')
              ->groupby('id')
              ->get();

              $totalAmount=$sales->sum('totalAmount');

What is your table structure and the desired result?

@TRAY2 - my table is looks like this

                      'address_id','product_name','quantity','price','amount'

                   $table->increments('id');
                 $table->integer('address_id')->unsigned();
               $table->string('product_name');
              $table->integer('quantity');
             $table->integer('price');
             $table->integer('amount');
             $table->timestamps();
            $table->foreign('address_id')->references('id')->on('addressitems');

So something along the lines of

SELECT s.*,
           (SELECT SUM(s1.amount) 
        FROM sales s1
            WHERE DATE(s1.created_at) = CURDATE()) sales_today,
           (SELECT SUM(s2.amount) 
        FROM sales s2
            WHERE DATE(s3.created_at`) = CURDATE()  - INTERVAL 1 DAY))  sales_yesterday
FROM sales s

@TRAY2 - which something along the line, can you arrange like above code?

I'm not that fluent in Eloquent yet but you can use DB::rawor DB::selectRaw` .

https://laravel.com/docs/5.8/queries#raw-expressions

An eloquent takes this form, just example here, take the example and re-work with your data. You will sum instead of count and you need:

->whereBetween('date', [$date_from, $date_to])
->selectRaw('sum(amount) as amt')  / just example

You have to work out the variables to use in $date_from, $date_to.

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Look at the QB examples, and realize some "trial and error" may be required.

https://laravel.com/docs/5.8/queries

ORM has all QB methods build in.

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

Note practice some simpler QB queries first.

this is what i want to archive on the below formula in dail report

              formula for Closed balance =cash sales(per day)+cash received(customer)- expenses

and tomorrow we need to have something like this

          closed balance(2moro)=closed balance(yesterday)+cash 
             sales+ cash received(customer)-expenses

my controller

                  $sales = DB::table('Sales')->select(DB::raw('*'),DB::raw('sum(amount) as totalAmount'))
              ->whereRaw('Date(created_at) = CURDATE()')
              ->groupby('id')
              ->get();

              $totalAmount=$sales->sum('totalAmount');


    $Expenses = DB::table('Expenses')->select(DB::raw('*'),DB::raw('sum(amount) as totalAmountexp'))
              ->whereRaw('Date(created_at) = CURDATE()')
              ->groupby('eid')
              ->get();

              $totalexpenses=$Expenses->sum('totalAmountexp');

//Mikopo
     $mikopo = DB::table('customers')->select(DB::raw('*'),DB::raw('sum(amount) as totalm'))
              ->whereRaw('Date(created_at) = CURDATE()')
              ->groupby('id')
              ->get();

              $totalmikopo =$mikopo->sum('totalm');

 
   //payment
   $payment = DB::table('payments')->select(DB::raw('*'),DB::raw('sum(amount) as totalM'))
              ->whereRaw('Date(created_at) = CURDATE()')
              ->groupby('id')
              ->get();

              $totalpayment=$payment->sum('totalM');

and this is show me per day report but, i can not manage to remain with closed balance inroder to use it next day

Another example.

A join and group by:

Looks like this when looped over:

Code:

public function monthlyReport()
    {
        $bdate = Request::input('begindate');
        $edate = Request::input('enddate');

        $sql = "select distinct `account_types`.`AccountType` AS `AccountType`,`accounts`.`AccountNumber` AS `AccountNumber`,`accounts`.`AccountName` AS `AccountName`,sum(`transactions`.`Expense`) AS `Sum_Expense`,sum(`transactions`.`Income`) AS `Sum_Income` from ((`account_types` join `accounts` on((`account_types`.`AccountTypeID` = `accounts`.`AccountTypeID`))) join `transactions` on((`accounts`.`AccountID` = `transactions`.`AccountID`))) where (`transactions`.`TransactionDate` Between '$bdate' and  '$edate') group by `account_types`.`AccountType`,`accounts`.`AccountNumber`,`accounts`.`AccountName`";
        
        $sth = DB::getPdo()->prepare($sql);
        $sth->execute();
        $quy = $sth->fetchAll(\PDO::FETCH_OBJ);
        $title = 'Monthly Report';
        return view('account.report', compact('quy', 'title'));
    }

If image not showing here:

https://i.imgur.com/RlzeSEp.jpg

Some logic is required during the loop. And this was a rough draft, not final version.

Attempting to point you in the right direction, but I don't believe in writing someone else's query, study this stuff in the documentation work the examples realize it's going to take some trial-and-error and you will get it.

Work the examples in the query Builder section of the documentation.

There's probably not anyone here that can whip out a complex query the first time it does take trial-and-error to work out the bugs for anyone.

Please don't look for a copy and paste solution rather learn this stuff.

Above example I did not bind parameters because they are dates however anytime you use regular PDO or db facade bind those parameters.

Here is an example of db facade: https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

Another QB example: https://laracasts.com/discuss/channels/eloquent/orderby-computed-related-attribute