CODEWITHSUNDEEP
mysqlsql
shhasan
shhasan

Reputation: 453

Why do I get incorrect result using SUM()

I've been trying to write a SQL query which sums columns. Here's the query:

SELECT `p`.`project_id`          AS `project_id`, 
       Sum(`r`.`expected_cost`)  AS `total_expected_cost`, 
       Sum(`r`.`expected_delay`) AS `total_expected_delay`, 
       Sum(`resp`.`cost`)        AS `project_total_mitigation_cost` 
FROM   ( ( ( `risks` `r` 
             JOIN `tasks` `t` 
               ON (( `t`.`task_id` = `r`.`task_id` )) ) 
           JOIN `projects` `p` 
             ON (( `t`.`project_id` = `p`.`project_id` )) ) 
         LEFT JOIN `responses` `resp` 
                ON (( `resp`.`risk_id` = `r`.`risk_id` )) ) 
GROUP  BY `p`.`project_id`

The result of the summation for the columns is way too high. expected-cost, expected_delay, cost can not be NULL.

Sample Data:

**************************
        projects
**************************
project_id    project_name
    1             alpha
    2             beta
    3             gamma
    4             delta

*******************************************************
                    tasks
*******************************************************
    task_id    project_id     name        
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
     5              1         mobile        
     45             1         keyboard           
     9              3         bottle          
     20             4         card           

*********************************************************
                        risks
*********************************************************
    risk_id    task_id     expected_cost    expected_delay    
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
     1           5            1400.00        5.0
     2           45           300.00         4.0  
     3           9            10.00          10.0
     4           20           0.00           100.0

*******************************************************
                        responses
*******************************************************
    risk_id    response_id     cost        
+++++++++++++++++++++++++++++++++++++++++++++++++++++++
     1         7              1500.00        
     2         9              900.00           
     3         13             80.00          
     4         2              1.00           

*********************************************************************************************
                                 desired result of query
*********************************************************************************************
  project_id    total_expected_cost     total_expected_delay   project_total_mitigation_costs        
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
     1              1700.00                        9.0                     2400.00
     2              0.00                           0.0                     0.00
     3              10.00                          10.0                    80.00
     4              0.00                           100.0                   1.00

A little explanation to go with the sample data:

A project has tasks and tasks have risks and risks have responses. risks have expected costs and expected_delay. responses have costs.

I'm not sure where I'm going wrong. All help is greatly appreciated.

Upvotes: 2

Views: 95

Answers (2)

FuzzyTree
FuzzyTree

Reputation: 32392

I'm not sure what's causing your sums to return higher numbers but your parenthesis in your from clause might be off. It's best to remove them since they're not necessary.

Also, you should select from the projects table and left join the remaining tables because you want 1 row per project.

select p.project_id,
    sum(expected_cost) total_expected_cost,
    sum(expected_delay) total_expected_delay,
    sum(cost) project_total_mitigation_cost
from projects p
left join tasks t on t.project_id = p.project_id
left join responses r on r.project_id = p.project_id
group by p.project_id

Upvotes: 0

Sparky
Sparky

Reputation: 15085

To debug this, change the select clause to count(*)

  • Then run count on the first table
  • Add each join one at a time
  • If the count increases more than expected, you'll know which JOIN is causing the problem.

Once you get the joins resolved and the count(*) is proper, put the regular select clause back in

Upvotes: 1

Related Questions