Obodo

Thanks

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

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

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

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

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

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

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

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

Sorry. I've been away for a while. Thanks all. I will do as suggested. ps btw: i have been able to do the reset and delete as i planned initially but wont be implementing it. i will go with your suggestion. Thanks

Thanks. But I am not totally deleting the data, i am moving them to a different table. I am still trying to comprehend the issue of not updating the records since the goods are returned. if we don't update the record, it means the record we have is not correct as the returned goods will be taken to the store for reselling. so we will have an excess that is not reflecting on the record. how do you deal with that? unless of course we do a different record and collation and i feel is one-to-many exercise. what do you think?

Hello all, In my shopping app. I want add a delete feature from the admin area. In case there is a dispute for a transaction, the admin can delete the transaction. The problem I am having is that when the transaction is deleted, i want the items bought to be added to their respective total. i.e if i have 200 pepsi, 30 cocacola, 20 fruit juice in stock and a customer bought 2 pepsi, 2 cocacola, 3 juice. definately the total in stock will reduce. I want a situation where after i delete the transaction the items bought will be added back to its remaining quantity. previous quantity pepsi 200 coke 30 fruit 20 after purchase pepsi 180 coke 28 fruit 17 after delete pepsi 200 coke 30 fruit 20 thanks

In my database table, i have a field for price and quantity. Is there a way I could use a select to get 2 values from the database? What I mean is I will create a select field where when I select wholesale it will bring out the price and quantity and if I select retail, it will bring the price and the quantity.

I'm thinking aloud. Is there a way I could use a select to get 2 values from the database? What I mean is I will create a select field where when I select wholesale it will bring out the price per carton and the quantity available and if I select retail, it will bring the price per unit and the quantity available for the item. The values will be from the database of course I'm thinking jquery will be able to do such.

WOAH! I'm lost here! Will need to read and reread. Thanks