Wrong on summation of two columns

Question

I have created Table production_report

pro_id	pro_stage_id	pro_model_id	pro_qty
1	1	44	420
2	1	44	200
3	1	44	430

Table production_defects_report

pdr_id	prd_modet_id	pdr_stage_id	pdr_defect_id	pdr_qty
1	44	1	2	123
2	44	1	2	123

Table model_details

modet_id	modet_name
44	22955 TMR5 BLUE

Table stages

st_id	st_name
1	Prepare Material
2	Testing

Table defects

def_id	def_name
1	Broken Head
2	Lose power

I need to know the summation of pro_qty and the pdr_qty to know how much produced and how many defects founded after testing such as the following table

Model Name	Stage Name	Produced Quantity	Defect Name	Quantity of defects	Percentage
22955 TMR5 BLUE	Prepare Material	1050	Lose power	246	35.14

I have created a Model to get and summation in QTY from both tables as the below function

public function getCombinedReport() {
        $this->db->select('md.modet_desc AS model_name, s.st_name AS stage_name, SUM(pr.pro_qty) AS total_pro_qty, d.def_name AS defect_name, COALESCE(SUM(pdr.pdr_qty), 0) AS total_pdr_qty');
        $this->db->from('production_report AS pr');
        $this->db->join('model_details AS md', 'pr.pro_model_id = md.modet_id');
        $this->db->join('stages AS s', 'pr.pro_stage_id = s.st_id');
        $this->db->join('production_defects_report AS pdr', 'pr.pro_model_id = pdr.pdr_modet_id AND pr.pro_stage_id = pdr.pdr_stage_id');
        $this->db->join('defects AS d', 'pdr.pdr_defect_id = d.def_id');
        $this->db->group_by('pr.pro_model_id, pr.pro_stage_id, d.def_id');
        $query = $this->db->get();
        return $query->result();
    }

The problem i found it returns the summation of pro_qty and pdr_qty multiplied by the counted rows of other joined tables. such as the following table.

Model Name	Stage Name	Produced Quantity	Defect Name	Quantity of defects	Percentage
22955 TMR5 BLUE	Prepare Material	2100	Lose power	738	35.1%

I want to generate a report to know how many defects for each stage in the model.

Answer 1

You're getting the wrong results because you have multiple rows with the same model_id and stage_id in the production_report table, and also multiple rows with the same model_id, stage_id and defect_id in the production_defects_report table. If you then join these two tables on model_id and stage_id, sql calculates all possible combinations of rows from the production_report and production_defects_report table where the model_id and stage_id match, and that causes it to duplicate rows. If you then sum the rows you also count the duplicates.

What you could do, is first group the production_report table on model_id and stage_id and calculate the quanties for each group, and also group the production_defects_report table on model_id, stage_id and defect_id and calculate the quantities for each group, before joining the two tables. Then there will no longer be multiple rows with the same model_id and stage_id (unless the defect_id is different, but I suppose you do want a separate row in that case) so sql will no longer duplicate the rows and since you've already done the summation before the join, you don't need to group and sum in the outer query anymore either. Query:

SELECT md.modet_name, s.st_name AS stage_name, pr.sum_pro_qty AS total_pro_qty, d.def_name AS defect_name, COALESCE(pdr.sum_pdr_qty, 0) AS total_pdr_qty
FROM (SELECT pro_model_id, pro_stage_id, SUM(pro_qty) AS sum_pro_qty
    FROM production_report
    GROUP BY pro_model_id, pro_stage_id) AS pr
JOIN model_details AS md ON pr.pro_model_id = md.modet_id
JOIN stages AS s ON pr.pro_stage_id = s.st_id
JOIN (SELECT pdr_modet_id, pdr_stage_id, pdr_defect_id, SUM(pdr_qty) AS sum_pdr_qty
    FROM production_defects_report 
    GROUP BY pdr_modet_id, pdr_stage_id, pdr_defect_id) AS pdr
ON pr.pro_model_id = pdr.pdr_modet_id AND pr.pro_stage_id = pdr.pdr_stage_id
JOIN defects AS d ON pdr.pdr_defect_id = d.def_id

Fiddle: https://www.db-fiddle.com/f/8DgUxDDZ3Jda4U6fwgiL6t/0 (Click "run" in the top bar to see the result.)

In CodeIgniter 3:

$this->db->query(
    'SELECT md.modet_name, s.st_name AS stage_name, pr.sum_pro_qty AS total_pro_qty, d.def_name AS defect_name, COALESCE(pdr.sum_pdr_qty, 0) AS total_pdr_qty
    FROM (SELECT pro_model_id, pro_stage_id, SUM(pro_qty) AS sum_pro_qty
        FROM production_report
        GROUP BY pro_model_id, pro_stage_id) AS pr
    JOIN model_details AS md ON pr.pro_model_id = md.modet_id
    JOIN stages AS s ON pr.pro_stage_id = s.st_id
    JOIN (SELECT pdr_modet_id, pdr_stage_id, pdr_defect_id, SUM(pdr_qty) AS sum_pdr_qty
        FROM production_defects_report 
        GROUP BY pdr_modet_id, pdr_stage_id, pdr_defect_id) AS pdr
    ON pr.pro_model_id = pdr.pdr_modet_id AND pr.pro_stage_id = pdr.pdr_stage_id
    JOIN defects AS d ON pdr.pdr_defect_id = d.def_id'
)->result();