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

Question

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

Answer 1

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>