How do MySQL aggregate sum function with two different data tables?-CodePudding

 SELECT
    category_id,
    product_size,
    category_name,
    SUM(product_quantity) AS total_quantity
FROM tbl_categories_quantity  -- (table-1)
INNER JOIN tbl_categories USING (category_id)
GROUP BY category_id,product_size

The Above Code is working in a single table, and I want to Add the below code (2nd table) that does not work

SELECT
    category_id,
    product_size,
    SUM(product_sell) AS total_sell
FROM tbl_product_sell -- (table-2)
GROUP BY category_id,product_size;

[![enter image description here][5]][5]

 THANKS Advance

CodePudding user response：

From first subquery retrieves category and product size wise total quantity and second one retrieves total sales based on category and product size. Then combine this two subquery with LEFT JOIN because sometimes sale may not happen. COALESCE() is used for replacing NULL value to 0 (zero). If specific category or product size wise data are required then use WHERE clause in both the subquery. As category id is unique so MAX(category_name) is used otherwise category name must be placed in GROUP BY clause. Subtract total sale from total quantity for calculating available quantity.

   -- MySQL
    SELECT t.category_name category
         , t.product_size
         , t.product_quantity
         , COALESCE(p.total_sell, 0) product_sell
         , (t.product_quantity - COALESCE(p.total_sell, 0)) available_in_stock
    FROM (SELECT tc.category_id
               , tcq.product_size
               , MAX(tc.category_name) category_name
               , SUM(tcq.product_quantity) product_quantity
          FROM tbl_categories tc
          INNER JOIN tbl_categories_quantity tcq
                  ON tc.category_id = tcq.category_id 
          GROUP BY tc.category_id
                 , tcq.product_size) t
    LEFT JOIN (SELECT category_id
                    , product_size
                    , SUM(product_sell) total_sell
               FROM tbl_stock_sell
               GROUP BY category_id
                      , product_size) p
           ON t.category_id = p.category_id
          AND t.product_size = p.product_size

Please check from url https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=b8c54aa656d9dc930fcb7a93d2bc0960

N.B.: Table name or column name may vary based on your DB.

CodePudding user response：

I think you can use next query:

 SELECT
    quantity.category_id,
    quantity.product_size,
    category_name,
    SUM(quantity.product_quantity) AS total_quantity,
    total_sell,
    (total_quantity - total_sell) AS in_stock
FROM tbl_categories_quantity quantity
LEFT JOIN ( -- join second table
  SELECT
    category_id,
    product_size,
    SUM(product_sell) AS total_sell
  FROM tbl_product_sell
  GROUP BY category_id, product_size
) sell ON sell.category_id = quantity.category_id AND product_size = quantity.product_size 
INNER JOIN tbl_categories ON quantity.category_id = tbl_categories.category_id
GROUP BY quantity.category_id, quantity.product_size;