根据日期和名称对行进行汇总

I'm trying to SUM the quantity of items according to its name from one table and then SUM the product of items.

I have two table (Bars and drinksales) as shown.

1: Bars

id |  name  |cost|
 1   item1   2000
 2   item2   5000

2: Drinksales

id | drink | no_drinks | date
1    item2     2         2018-08-01
2    item1     2         2018-08-01
3    item2     2         2018-08-01
4    item2     1         2018-08-01

My aim here is to SUM no_drinks of item2 acording to date which suppose to be (5) and item1 suppose to be (2), from there I have to run a query that will fetch cost of item1 and item2 from "Bar" table. I need results like this

(5*5000)+ (2*2000) = 29000

Here my scripts, every thing about connection its okay.

1: Reportcontroler.php

   $resx=DB::table('drinksales')
                ->join('bars','bars.name', '=' ,'drinksales.drink')
                ->where('date','LIKE','%'.$date.'%')
                ->get(array(
                    'bars.cost',
                    DB::raw('SUM(bars.cost) AS costs'),
                    DB::raw('SUM(drinksales.no_drinks) AS no_drinks')
                ));
            if($resx){
            foreach ($resx as $row) {
                $datas = array(
                    'amount' => $row->costs,
                    'no_drinks' => $row->no_drinks

                );
            }
                return View::make('reports.restaurantsreportcostd',$datas);
            }
            $datas=array(
                'amount'=>'No money collected'
            );
            return View::make('reports.restaurantsreportcostd',$datas);

After query above scripts I get 119000, which is not my desired answer.

Here a view file

2: reports.restaurantsreportcostd

        <p class="alert alert-success text-center">Total income {{$amount*$no_drinks}} /= </p>

Any help please, and sorry if am not explain well

It was calculating wrong because all it did was total up all costs and then total up all drinks and multiplied it.

I redid your logic to achieve the results you were looking for like so:

$resx = DB::table('drinksales')
    ->join('bars', 'bars.name', '=', 'drinksales.drink')
    ->where('date', 'LIKE', '%' . $date . '%')
    ->get();

$datas = array(
    'amount' => 0,
    'no_drinks' => 0,
    'total_income' => 0
);

if ($resx) {
    foreach ($resx as $row) {
        $datas['amount']+= $row->cost;
        $datas['no_drinks']+= $row->no_drinks;
        $datas['total_income']+= $row->cost * $row->no_drinks;
    }
} else {
    $datas['amount'] = 'No money collected';
}

return View::make('reports.restaurantsreportcostd', $datas);

You can now get the total income directly with the additional variable I set as such:

<p class="alert alert-success text-center">Total income {{ $total_income }} /= </p>

Use the group by statement please try this. SELECT SUM(Drinnksales.no_drinks * COST)INSERT YOUR JOIN CODE HEREGROUP BY Drinksales.Drink