Sum and join 2 tables

[Obodo]

I want to sum and join 2 tables. I am getting a wrong answer. What am i doing wrong please.

what i want is to get the sum of quantity bought and the sum of product quantity so that when i minus the product quantity for the quantity bought, i will get the balance which is the difference between the product quantity - quantity bought = available quantity

$stmt = $pdo->query("
   SELECT 
   a.prod_name, a.prod_size,
   b.prod_name, b.prod_size,
   SUM(b.qty_bought) qty_bot,
   SUM(a.prod_qty) qty_received
   FROM tbl_distribution a
   JOIN tbl_sales_bar b
   ON a.staff_id = b.staff_id
   WHERE a.staff_id = '$_GET[id]'
   GROUP BY  b.prod_id
");
WHILE($row = $stmt->fetch(PDO::FETCH_ASSOC)){
  echo '<tr>
           <td>'.$row["prod_name"].'</td>           
           <td>'.$row["prod_size"].'</td>
           <td>'.$row["qty_received"].'</td>
           <td>'.$row["qty_bot"].'</td>
           <td></td>
           </tr>';
}

Image shows what i want to achieve

tbl_distribution - the table for distribution
tbl_distribution_user - the table for a single user after sum and group
tbl_sales_bar - table for sales
tbl_sales_bar_user - table for a single user in bar after sum and group
result - what i intent to achieve as my end result after summing and grouping

thanks

Edited January 31, 2023 by Obodo

[Barand]

Your query joins each record from 'a' to many records from 'b' thus multiplying your totals.

Try

SELECT a.prod_id, a.prod_name
     , a.qty_received
     , b.qty_bot
     , a.qty_received - b.qty_bot as qty_remain
FROM (
        SELECT prod_id
             , prod_name
             , sum(prod_qty) as qty_received
        FROM tbl_distribution
        WHERE staff_id = 2962
        GROUP BY prod_id
      ) a 
      LEFT JOIN 
      (
        SELECT prod_id
             , sum(qty_bought) as qty_bot
        FROM tbl_sales_bar
        WHERE staff_id = 2962
        GROUP BY prod_id
      ) b USING (prod_id);

Your tables are in dire need of normalization. For example, "prod_name" should occur once in your database (in a product table) and not be repeated in several tables.

PS

In future, don't post pictures of your data, post the data. It was not a great deal of fun building the test tables from those images so that I could test your  query.

[Obodo]

3 hours ago, Barand said:

Your query joins each record from 'a' to many records from 'b' thus multiplying your totals.

Try

SELECT a.prod_id, a.prod_name
     , a.qty_received
     , b.qty_bot
     , a.qty_received - b.qty_bot as qty_remain
FROM (
        SELECT prod_id
             , prod_name
             , sum(prod_qty) as qty_received
        FROM tbl_distribution
        WHERE staff_id = 2962
        GROUP BY prod_id
      ) a 
      LEFT JOIN 
      (
        SELECT prod_id
             , sum(qty_bought) as qty_bot
        FROM tbl_sales_bar
        WHERE staff_id = 2962
        GROUP BY prod_id
      ) b USING (prod_id);

Your tables are in dire need of normalization. For example, "prod_name" should occur once in your database (in a product table) and not be repeated in several tables.

PS

In future, don't post pictures of your data, post the data. It was not a great deal of fun building the test tables from those images so that I could test your  query.

Sorry for the images used. just wanted to drive home my intentions. Noted though.

I will try and normalize the table more. I am just trying to avoid too many JOINs.

Thank you so very much cos this solved my question.

There is something else i was trying but was getting error though. I was thinking a UNION could achieve same result?

Dont want to start another discussion since the issue is solved else i would have posted my code so you could see and maybe tell what is wrong with it?

Can i post the code?

 

Thanks anyways

[Barand]

If you have a different problem, post it in a new topic.

[Obodo]

42 minutes ago, Barand said:

If you have a different problem, post it in a new topic.

OK. But it's the same issue I am trying to solve but with a different approach. I am just wondering why it did not work. Maybe I should just let go and stick with what you gave. Just wanted to learn more and feed my curiosity 

[Barand]

OK, post it and I'll have look.

[Obodo]

12 hours ago, Barand said:

OK, post it and I'll have look.

 

SELECT a.prod_name, a.prod_size, b.prod_name, b.prod_size,
  ifnull(b.qty_bot,0), ifnull(a.qty_received ,0)
  from (
     SELECT a.prod_id prod_id
     FROM tbl_distribution a
     UNION
     SELECT b.prod_id
     FROM tbl_sales_bar b
  ) t
  LEFT JOIN (
        SELECT
        a.prod_id, a.prod_name, a.prod_size,
        SUM(a.prod_qty) qty_received
        FROM tbl_distribution a
        WHERE a.staff_id = 2962
        GROUP BY  a.prod_id
  ) a on a.prod_id = b.prod_id
  LEFT JOIN (
         SELECT b.prod_id, b.prod_name, b.prod_size,
         SUM(b.qty_bought) qty_bot
         FROM tbl_sales_bar b
         WHERE b.staff_id = 2962
         GROUP BY  b.prod_id
  ) b on b.prod_id = t.prod_id"

Gave an error

Edited February 1, 2023 by Obodo

[Barand]

You are referencing aliases before they have been defined. For example, you dont define the table alias "b" until the last line of the query but you are referencing it several time earlier subqueries in the query.

Why output identical values twice? ...

 

SELECT a.prod_name
     , a.prod_size 
     , ifnull(a.qty_received, 0) qty_received
     , ifnull(b.qty_bot, 0) qty_bot
     , ifnull(a.qty_received, 0) - ifnull(b.qty_bot, 0) qty_remain
  FROM (
     SELECT prod_id
     FROM tbl_distribution
     UNION
     SELECT prod_id
     FROM tbl_sales_bar
  ) t
  LEFT JOIN (
        SELECT
        prod_id, prod_name, prod_size,
        SUM(prod_qty) qty_received
        FROM tbl_distribution
        WHERE staff_id = 2962
        GROUP BY  prod_id
  ) a on a.prod_id = t.prod_id
  LEFT JOIN (
         SELECT prod_id,
         SUM(qty_bought) qty_bot
         FROM tbl_sales_bar
         WHERE staff_id = 2962
         GROUP BY prod_id
  ) b on b.prod_id = t.prod_id;
  

results

+---------------+-----------+--------------+---------+------------+
| prod_name     | prod_size | qty_received | qty_bot | qty_remain |
+---------------+-----------+--------------+---------+------------+
| 33            |           |           13 |       8 |          5 |
| Star Wrangler |           |            7 |       6 |          1 |
| Star          |           |           19 |       7 |         12 |
| Goldberg      |           |           10 |       0 |         10 |
+---------------+-----------+--------------+---------+------------+

 

[Obodo]

On 1/31/2023 at 2:20 PM, Barand said:

Your query joins each record from 'a' to many records from 'b' thus multiplying your totals.

Try

SELECT a.prod_id, a.prod_name
     , a.qty_received
     , b.qty_bot
     , a.qty_received - b.qty_bot as qty_remain
FROM (
        SELECT prod_id
             , prod_name
             , sum(prod_qty) as qty_received
        FROM tbl_distribution
        WHERE staff_id = 2962
        GROUP BY prod_id
      ) a 
      LEFT JOIN 
      (
        SELECT prod_id
             , sum(qty_bought) as qty_bot
        FROM tbl_sales_bar
        WHERE staff_id = 2962
        GROUP BY prod_id
      ) b USING (prod_id);

Your tables are in dire need of normalization. For example, "prod_name" should occur once in your database (in a product table) and not be repeated in several tables.

PS

In future, don't post pictures of your data, post the data. It was not a great deal of fun building the test tables from those images so that I could test your  query.

I just noticed something on this. the goldberg ought to remain 10 but its null. since nothing is sold from the quantity received, the quantity remaining should be be quantity received

[Obodo]

17 hours ago, Barand said:

You are referencing aliases before they have been defined. For example, you dont define the table alias "b" until the last line of the query but you are referencing it several time earlier subqueries in the query.

Why output identical values twice? ...

 

SELECT a.prod_name
     , a.prod_size 
     , ifnull(a.qty_received, 0) qty_received
     , ifnull(b.qty_bot, 0) qty_bot
     , ifnull(a.qty_received, 0) - ifnull(b.qty_bot, 0) qty_remain
  FROM (
     SELECT prod_id
     FROM tbl_distribution
     UNION
     SELECT prod_id
     FROM tbl_sales_bar
  ) t
  LEFT JOIN (
        SELECT
        prod_id, prod_name, prod_size,
        SUM(prod_qty) qty_received
        FROM tbl_distribution
        WHERE staff_id = 2962
        GROUP BY  prod_id
  ) a on a.prod_id = t.prod_id
  LEFT JOIN (
         SELECT prod_id,
         SUM(qty_bought) qty_bot
         FROM tbl_sales_bar
         WHERE staff_id = 2962
         GROUP BY prod_id
  ) b on b.prod_id = t.prod_id;
  

results

+---------------+-----------+--------------+---------+------------+
| prod_name     | prod_size | qty_received | qty_bot | qty_remain |
+---------------+-----------+--------------+---------+------------+
| 33            |           |           13 |       8 |          5 |
| Star Wrangler |           |            7 |       6 |          1 |
| Star          |           |           19 |       7 |         12 |
| Goldberg      |           |           10 |       0 |         10 |
+---------------+-----------+--------------+---------+------------+

 

woah! thanks a lot. this one accounts for the 10 goldberg that was not sold

[Obodo]

Thanks for help and pointers. your method seems easier but for the null value on goldberg.

i am going to work on the normalization of the tables.

i will like to adapt to your method. how can the null issue be resolved?
i am thinking might ifnull do the magic? lemme give it a shot though

Edited February 2, 2023 by Obodo

[Obodo]

On 1/31/2023 at 2:20 PM, Barand said:

Your query joins each record from 'a' to many records from 'b' thus multiplying your totals.

Try

SELECT a.prod_id, a.prod_name
     , a.qty_received
     , b.qty_bot
     , a.qty_received - b.qty_bot as qty_remain
FROM (
        SELECT prod_id
             , prod_name
             , sum(prod_qty) as qty_received
        FROM tbl_distribution
        WHERE staff_id = 2962
        GROUP BY prod_id
      ) a 
      LEFT JOIN 
      (
        SELECT prod_id
             , sum(qty_bought) as qty_bot
        FROM tbl_sales_bar
        WHERE staff_id = 2962
        GROUP BY prod_id
      ) b USING (prod_id);

Your tables are in dire need of normalization. For example, "prod_name" should occur once in your database (in a product table) and not be repeated in several tables.

PS

In future, don't post pictures of your data, post the data. It was not a great deal of fun building the test tables from those images so that I could test your  query.

like i thought, the ifnull did the magic. i was able to get the qty_remaining but i couldnt get the qty_bot to be 0. still showing null. i guess i just have to stick with the null of is there a way i could get 0 and not null?

SELECT 
a.prod_id, a.prod_name , a.qty_received , 
b.qty_bot , a.qty_received - ifnull(b.qty_bot, 0) as qty_remain 
FROM ( SELECT prod_id , prod_name , sum(prod_qty) as qty_received 
FROM tbl_distribution 
WHERE staff_id = 2962 GROUP BY prod_id ) a 
LEFT JOIN ( SELECT prod_id , sum(qty_bought) as qty_bot 
FROM tbl_sales_bar 
WHERE staff_id = 2962 GROUP BY prod_id ) b 
USING (prod_id);

 

[Barand]

7 hours ago, Obodo said:

but i couldnt get the qty_bot to be 0.

Have you considered using ifnull() on that too?

SELECT ..., ifnull(b.qty_bot, 0) as qty_bot, ...

Or an aternative to isnull() is coalesce()

SELECT ..., coalesce(b.qty_bot, 0) as qty_bot, ...

 

[Obodo]

23 hours ago, Barand said:

Have you considered using ifnull() on that too?

SELECT ..., ifnull(b.qty_bot, 0) as qty_bot, ...

Or an aternative to isnull() is coalesce()

SELECT ..., coalesce(b.qty_bot, 0) as qty_bot, ...

 

Thanks