Group by sum from two different table with the same id

I have created a simple butchery web application which has two tables: sells and bought.

The bought table will include the kilos and price of each meat the user bought ,and the sells table will also include the kilos and the price of each meat sold.

Each and every meat has their own id so for example if the user bought a goat which has an id of 2 it will insert in the column of meat id and if they sell the goat it will insert the same id in the column of meat id for sells table

Now what i wanted to do is create a simple analytic where when the user chooses the month from the checkbox it will give them the total amount of kilos,price bought and total amount of kilos,price sold for that selected month, so that the user can see how much profit they made for each meat and how much kilos they lost.

The user can choose multiple months and compare the stats. For example, if the user clicks on the checkbox of April and May ,it should give them the total amount of kilos bought and sold for those two months

bought table

sell table

My sql code

public function getkiloleft($month){


  
  $month_filter = implode("','",  $month);

    
    $query = "
              WHERE sell.month IN('". $month_filter."')
              AND bought.month IN('". $month_filter."')
              GROUP BY bought.meat_id ,sell.m_id
              
         ";
   
   $this->db->query('SELECT
                      bought.id as b_id, 
                      sell.id as s_id , 
                      sell.name,
                      bought.name,

                      SUM(sell.sold) AS sold,
                      SUM(bought.bought) AS bought,


                      SUM(sell.s_kilo) AS sk,
                      SUM(bought.b_kilos)AS bk
                FROM sell
                inner JOIN bought ON bought.meat_id =  sell.m_id
                '.$query.'
        ');



        $results = $this->db->resultSet();
        return $results;

}

code to fetch result

   $results = $this->FabsModel->getkiloleft($month);
  
      foreach ($results as $rs){

      $profit= $rs->sold - $rs->bought;


       $kilos= $rs->sk - $rs->bk;


        

    $data.="<p>
                      name of meat: <b> $rs->name</b>
                      <br><br>
                      bought for: ".number_format($rs->bought,1)." $<br>
                      kilos  bought: $rs->bk KG<br>
                      <br><br>

                      sold for :  $rs->sold $<br>
                      kilos sold:  ".number_format($rs->sk,1)." kg<br>
                      <br><br>
                      profit:    ".number_format($profit,1)." $<br>
                      kilos left:    $kilos KG
                          
                         </p>
                         
                         <hr>";
   }


echo $data;

This is the result i get when check on April

This is the result i get when check on April and May

based on the result of April it shows that kilos and price multiply twice and i don’t want that to happen and when i check on April and May the result keeps on multiplying

Result i want when i on check April should be

 name of meat: goat

bought for: 20.1 $
kilos bought: 10.1 KG


sold for : 5 $
kilos sold: 3.0 kg


profit: -15.1 $
kilos left: 7.1 KG



name of meat: lamb

bought for: 11.0 $
kilos bought: 15.1 KG


sold for : 6.6 $
kilos sold: 4.6 kg


profit: -4.4 $
kilos left: 10.5 KG

Result i want when i click on April and May

 name of meat: goat

bought for: 22.1 $
kilos bought: 11.1 KG


sold for :  8.2$
kilos sold: 4.0 kg


profit: -13.9 $
kilos left: 7.1 KG



name of meat: lamb

bought for: 11.0 $
kilos bought: 15.1 KG


sold for : 11.6 $
kilos sold: 7.6 kg


profit: 0.6 $
kilos left: 7.5 KG

Answer

This is a bit more complicated that it might seem. Joining, then aggregating, would multipy the amounts if there are multiple rows per customer in both tables. You must also consider the possibility that a user may have matches in only one of the two tables.

I woulud recommend union all:

select id, name, sum(sold) sold, sum(s_kilos) s_kilos, sum(bought) bought, sum(b_kilos) b_kilos
from (
    select meat_id id, name, sold, s_kilos, 0 bought, 0 b_kilos from sold
    union all select m_id, name, 0, 0, bought, b_kilos from bought
) t
where ...
group by id, name

In a real life situation, you woulud have a reference table for the meats, that you could left join from:

select m.id, m.name, s.sold, s.s_kilos, b.bought, b.b_kilos 
from meats m
left join (
    select meat_id, sum(sold) sold, sum(s_kilos) s_kilos
    from sold
    group by meat_id
) s on s.meat_id = m.id
    left join (
    select m_id, sum(bought) bought, sum(b_kilos) b_kilos
    from bought
    group by m_id
) b on b.m_id = m.id

Advertisement

Answer