Jump to content

Sum and join 2 tables


Obodo
Go to solution Solved by Barand,

Recommended Posts

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
result.jpg.4c31a2de10fc4721f34e76f55ba0aa2f.jpg

tbl_distribution.jpg

tbl_distribution_user.jpg

tbl_sales_bar.jpg

tbl_sales_bar_user.jpg

Edited by Obodo
Link to comment
Share on other sites

  • Solution

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);

image.png.62beebd6d73f32efeca898d52c37602d.png

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.

Link to comment
Share on other sites

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);

image.png.62beebd6d73f32efeca898d52c37602d.png

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

Link to comment
Share on other sites

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 

Link to comment
Share on other sites

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 by Obodo
Link to comment
Share on other sites

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? ...

image.png.0657e450c1525e4c945c8ad38b9ee9f0.png

 

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 |
+---------------+-----------+--------------+---------+------------+

 

  • Like 1
Link to comment
Share on other sites

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);

image.png.62beebd6d73f32efeca898d52c37602d.png

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

Link to comment
Share on other sites

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? ...

image.png.0657e450c1525e4c945c8ad38b9ee9f0.png

 

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

Link to comment
Share on other sites

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 by Obodo
Link to comment
Share on other sites

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);

image.png.62beebd6d73f32efeca898d52c37602d.png

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);

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.