# Sum and join 2 tables

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,
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_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 by Obodo
##### 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
, b.qty_bot
, a.qty_received - b.qty_bot as qty_remain
FROM (
SELECT prod_id
, prod_name
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.

##### 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
, b.qty_bot
, a.qty_received - b.qty_bot as qty_remain
FROM (
SELECT prod_id
, prod_name
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

##### Share on other sites

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

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

##### Share on other sites

OK, post it and I'll have look.

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

```SELECT a.prod_name
, a.prod_size
, 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,
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 |
+---------------+-----------+--------------+---------+------------+```

##### 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
, b.qty_bot
, a.qty_received - b.qty_bot as qty_remain
FROM (
SELECT prod_id
, prod_name
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

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

```SELECT a.prod_name
, a.prod_size
, 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,
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

##### 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
##### 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
, b.qty_bot
, a.qty_received - b.qty_bot as qty_remain
FROM (
SELECT prod_id
, prod_name
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
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);```

##### Share on other sites

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, ...`

##### Share on other sites

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

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

×   Pasted as rich text.   Restore formatting

Only 75 emoji are allowed.