MySQL-Query for getting a report

Question

I am working on The mySQL database. Here is my DB-Fiddle. Need output like below.

I have used the below query

select 
    a.product_name as 'Product Name', 
    a.product_model 'Product Model', 
    c.batch_id as 'Batch', 
    b.rate as 'Sale Price', 
    c.supplier_rate as 'Purchase Price', 
    sum(b.quantity) as 'IN-QTY', 
    sum(c.quantity) as 'OUT-QTY', 
    (sum(b.quantity)-sum(c.quantity)) as 'Stock', 
    (sum(b.quantity)-sum(c.quantity)*b.rate) as 'Stock Sale Price', 
    (sum(b.quantity)-sum(c.quantity)*c.supplier_rate) as 'Stock Purchase Price'
from 
    product_information a 
    left join product_purchase_details b on a.product_id = b.product_id
    left join invoice_details c on a.product_id = c.product_id
GROUP BY 
    b.batch_id

Output

In the above image the In-QTY, Out-Qty, Stock, Stock Sale Price and Stock Purchase Price is wrong.

Update 1

The fiddle has 5 tables and from the below information I have tried to carry out my desired result

1. Product Name and Product Model from product_infortmation table
2. Batch from invoice_details or product_purchase_details
3. Sale price is the price entered at the time of purchase. So it's the rate from product_purchase_details table
4. Purchase price is the supplier_rate taken from invoice_details table
5. In-Qty is the quantity at the time of purchase and taken from product_purchase_details table
6. Out-Qty is the quantity at the time of invoice taken from the invoice_details table.
7. Stock is In-QTY - Out-Qty
8. Stock Sale Price is Stock * Sale Price
9. Stock Purchase Price is IN-QTY * Purchase Price

Update 2 Updated The Fiddle

Select   Product_Name,Product_Model,Batch,Sale_Price,Purchase_Price,IN_QTY,OUT_QTY, (IN_QTY-OUT_QTY) as STOCK,((IN_QTY-OUT_QTY)*Sale_Price)as Stock_Sale_Price, IN_QTY*Purchase_Price as Stock_Purchase_Price
from
(SELECT b.product_name as 'Product_Name',b.product_model as 'Product_Model',a.batch_id as 'Batch',sum(a.quantity) as 'IN_QTY',a.rate as 'Purchase_Price',0 as 'Sale_Price',
0 as 'OUT_QTY'
FROM `product_purchase_details` a
inner join product_information b on a.product_id = b.product_id
GROUP by a.batch_id

UNION ALL

SELECT  0 as 'Product_Name',0 as 'Product_Model',0 as 'Batch',0 as 'IN_QTY',0 as 'Purchase_Price',a.supplier_rate as 'Sale_Price',sum(a.quantity) as 'OUT_QTY'
FROM invoice_details a
GROUP by a.batch_id 
)z
group by Batch

Output

Again the output is not what I desired.

Note There can be no sale of any particular batch.

Any help would be highly appreciated

Answer 1

The SUMs are too large, correct?

When JOINing and doing SUMs (or COUNTs, etc), please note that the JOIN is done first. This generates an intermediate file containing all relevant combinations of all the Joined tables. Then the SUMs (etc) are computed.

If a SUM should be against less than the Join of all the tables, then it needs to be computed in a subquery. Depending on the situation, it might be done in either of these ways:

SELECT ...,
       ( SELECT SUM(...) FROM ... ) AS total_foo,
       ...
    FROM ...
    JOIN ...

Or

SELECT ...,
       sums.total_foo,
       ...
    FROM ( SELECT SUM(...) AS total_foo, ... FROM ... ) AS sums
    JOIN ...

The first one usually works well if the SUM is coming from a single table, independent of the other tables.

The second is often better if you need to compute multiple SUMs in one table before combining with the other tables.

In either reformulation, it may be valid to have a JOIN in the subquery -- perhaps 2 tables are needed to get the correct tally.

Answer 2

As Rick James noted, it makes sense to aggregate the purchase and invoice information separately first and then join them. Here's what aggregating on the purchase looks like:

---

Purchase

SELECT
  a.product_name as 'Product Name',
  a.product_model as 'Product Model',
  b.batch_id as 'Batch ID',
  b.rate as 'Sale Price',
  SUM(b.quantity) as 'IN-QTY'
FROM
  product_information a
  JOIN product_purchase_details b
    ON a.product_id = b.product_id
GROUP BY
  1, 2, 3, 4
ORDER BY
  1, 2, 3;

Product Name	Product Model	Batch ID	Sale Price	IN-QTY
Dumbles	2022	1	400.00	3.00
Dumbles	2022	111	400.00	8.00
Dumbles	2022	112	500.00	5.00
Dumbles	2022	12345	400.00	500.00
Dumbles	2022	123456	400.00	50.00
Dumbles	2022	786	400.00	500.00
Dumbles	2022	78879	500.00	200.00
Dumbles	2022	799	600.00	10.00
Dumbles	2022	cxz	200.00	70.00
Dumbles	2022	fgd	400.00	15.00
Dumbles	2022	KBER	400.00	30.00
Dumbles	2022	poo	800.00	80.00
Dumbles	2022	pop	400.00	20.00
Dumbles	2022	vcx	400.00	40.00
Hammer Strength	124578	12348	500.00	500.00
Hammer Strength	124578	211	500.00	500.00
Hammer Strength	124578	212	500.00	200.00
Hammer Strength	124578	744	750.00	10.00
Hammer Strength	124578	788	500.00	500.00
Hammer Strength	124578	799	600.00	520.00
MD	1546	12345	400.00	500.00
MD	1546	123489	400.00	200.00
MD	1546	788788	400.00	100.00
MD	1546	czswe	1000.00	50.00
MD	1546	wer	400.00	25.00

---

Now, similarly for the invoice:

---

Invoice

SELECT
  a.product_name as 'Product Name',
  a.product_model as 'Product Model',
  c.batch_id as 'Batch ID',
  c.supplier_rate as 'Purchase Price',
  SUM(c.quantity) as 'OUT-QTY'
FROM
  product_information a
  JOIN invoice_details c
    ON a.product_id = c.product_id
GROUP BY
  1, 2, 3, 4
ORDER BY
  1, 2, 3;

Product Name	Product Model	Batch ID	Purchase Price	OUT-QTY
Dumbles	2022	12345	400	80.00
Dumbles	2022	123456	400	50.00
Dumbles	2022	78879	437.5	10.00
Hammer Strength	124578	211	564.286	1.00
Hammer Strength	124578	212	564.286	2.00
Hammer Strength	124578	744	564.286	6.00
Hammer Strength	124578	788	533.333	500.00
Hammer Strength	124578	799	533.333	10.00
Hammer Strength	124578	799	590	10.00
MD	1546	12345	400	10.00
MD	1546	123489	400	20.00

---

So you can join these tables by both the product id and the batch id before calculating the rest of the statistics. In the following example, I'm joining in a way that if the batch doesn't exist in the aggregated invoice, then the OUT-QTY and Purchase Price are set to 0.

---

Final

WITH tbl_purchase as (
  SELECT
    product_id,
    batch_id,
    rate,
    SUM(quantity) as quantity
  FROM
    product_purchase_details b
  GROUP BY
    1, 2, 3
),
tbl_invoice as (
  SELECT
    product_id,
    batch_id,
    supplier_rate,
    SUM(quantity) as quantity
  FROM
    invoice_details
  GROUP BY
    1, 2, 3
),
agg as (
SELECT
  b.product_id,
  b.batch_id,
  b.rate,
  CASE
    WHEN c.supplier_rate is NULL THEN 0
    ELSE c.supplier_rate
  END as 'supplier_rate',
  b.quantity as 'in_quantity',
  CASE
    WHEN c.quantity IS NULL THEN 0
    ELSE c.quantity
  END as 'out_quantity'
FROM
  tbl_purchase b
  LEFT JOIN tbl_invoice c
    ON b.product_id = c.product_id AND b.batch_id = c.batch_id
)
SELECT
  a.product_name as 'Product Name',
  a.product_model as 'Product Model',
  agg.batch_id as 'Batch',
  agg.rate as 'Sale Rate',
  agg.supplier_rate as 'Purchase Rate',
  agg.in_quantity as 'IN-QTY',
  agg.out_quantity as 'OUT-QTY',
  agg.in_quantity - agg.out_quantity as 'Stock',
  (agg.in_quantity - agg.out_quantity)*agg.rate as 'Stock Sale Price',
  (agg.in_quantity - agg.out_quantity)*agg.supplier_rate as 'Stock Purchase Price'
FROM
  product_information a
  LEFT JOIN agg
    ON a.product_id = agg.product_id
ORDER BY
  1, 2;

Product Name	Product Model	Batch	Sale Rate	Purchase Rate	IN-QTY	OUT-QTY	Stock	Stock Sale Price	Stock Purchase Price
Dumbles	2022	1	400.00	0	3.00	0	3.00	1200.0000	0
Dumbles	2022	111	400.00	0	8.00	0	8.00	3200.0000	0
Dumbles	2022	112	500.00	0	5.00	0	5.00	2500.0000	0
Dumbles	2022	12345	400.00	400	500.00	80.00	420.00	168000.0000	168000
Dumbles	2022	123456	400.00	400	50.00	50.00	0.00	0.0000	0
Dumbles	2022	786	400.00	0	500.00	0	500.00	200000.0000	0
Dumbles	2022	78879	500.00	437.5	200.00	10.00	190.00	95000.0000	83125
Dumbles	2022	799	600.00	0	10.00	0	10.00	6000.0000	0
Dumbles	2022	cxz	200.00	0	70.00	0	70.00	14000.0000	0
Dumbles	2022	fgd	400.00	0	15.00	0	15.00	6000.0000	0
Dumbles	2022	KBER	400.00	0	30.00	0	30.00	12000.0000	0
Dumbles	2022	poo	800.00	0	80.00	0	80.00	64000.0000	0
Dumbles	2022	pop	400.00	0	20.00	0	20.00	8000.0000	0
Dumbles	2022	vcx	400.00	0	40.00	0	40.00	16000.0000	0
Hammer Strength	124578	12348	500.00	0	500.00	0	500.00	250000.0000	0
Hammer Strength	124578	211	500.00	564.2860107421875	500.00	1.00	499.00	249500.0000	281578.71936035156
Hammer Strength	124578	212	500.00	564.2860107421875	200.00	2.00	198.00	99000.0000	111728.63012695312
Hammer Strength	124578	744	750.00	564.2860107421875	10.00	6.00	4.00	3000.0000	2257.14404296875
Hammer Strength	124578	788	500.00	533.3330078125	500.00	500.00	0.00	0.0000	0
Hammer Strength	124578	799	600.00	533.3330078125	520.00	10.00	510.00	306000.0000	271999.833984375
Hammer Strength	124578	799	600.00	590	520.00	10.00	510.00	306000.0000	300900
MD	1546	12345	400.00	400	500.00	10.00	490.00	196000.0000	196000
MD	1546	123489	400.00	400	200.00	20.00	180.00	72000.0000	72000
MD	1546	788788	400.00	0	100.00	0	100.00	40000.0000	0
MD	1546	czswe	1000.00	0	50.00	0	50.00	50000.0000	0
MD	1546	wer	400.00	0	25.00	0	25.00	10000.0000	0

---

I think this is what you're looking for - though it doesn't match your first screenshot.