Geek Answers Handbook

Finding the difference in the number of rows in two tables in MySQL

I have two tables, one stores the products and the quantity we bought, and the other sells. Thus, the current stock is the sum of all quantity columns in the purchased table minus the number of rows in the sales table. How can this be expressed in MySQL. Remember that there are many different products.

EDIT: To make this harder, I have one more requirement. I have a bought table, a sold table, but I also have a product table. I need a list of all the products, and I want to know the amount available for each product. The problem with current answers is that they only return products that we have sold or bought already. I want all the products.

+5
source share
3 answers

try it


SELECT inv_t.product_id, inventory_total-nvl(sales_total,0)
FROM 
  (SELECT product_id, sum(quantity) as inventory_total
   FROM inventory
   GROUP BY product_id) inv_t LEFT OUTER JOIN
  (SELECT product_id, count(*) AS sales_total 
   FROM sales 
   GROUP BY product_id) sale_t
  ON (inv_t.product_id = sale_t.product_id)

This is a better solution than several other published ones that do not take into account the fact that some products may not have corresponding rows in the sales table. You want to make sure that such products also appear in the results.

NVL is an Oracle-specific function that returns the value of the first argument if it is not null, in which case it returns the value of the second argument. All commercial DBMSs have equivalent functions - you can use CASE in MySQL with the same effect.

+6
source 
SELECT product AS prd, 
SUM(quantity) - 
  IFNULL((SELECT COUNT(*)
   FROM sells
   WHERE product = prd 
   GROUP BY product), 0)
AS stock 
FROM bought
GROUP BY product;

This also works when the quantity sold is 0.

0
source

I propose making the inventory and sale tables in the views so that they are reused and the final query is very simple. Obviously, the names of the fields and tables will need to change according to your schema.

--First view: list products and the purchased qty
create or replace view product_purchases as
select
  product_id
 ,sum(purchased_qty) as purchased_qty
from
  purchases
group by
  product_id;

--Second view: list of products and the amount sold    
create or replace view product_sales as
select
  product_id
 ,count(*) as sales_qty
from
  sales
group by
  product_id;

--after creating those two views, run this query:
select
  pp.product_id
 ,pp.purchased_qty - ps.sales_qty as on_hand_qty
from
  product_purchases pp
 ,product_sales ps
where ps.product_id = pp.product_id;
0
source

More articles:


All Articles