We help IT Professionals succeed at work.

mysql sum retrieving expected results+total results

levelninesports
on
Medium Priority
372 Views
Last Modified: 2012-05-07
Ok this may require too much detail to explain but perhaps someone knows the general reason this happens..
I am returning sums which basically is the number of records that have fields set to 1.  There are 5 fields that may be zero or 1 in a file which is referenced by another file with a lot of records.
The error is each of the 5 results returns not the sum expected but the sum expected+the total #of records that has any field set.
That is if there were 20 total product_ids that met the select requirements and of these 20 the # that had each of the 5 abilities was (13,20,4,1,19) what actually is returned is (33,40,24,1,39)
this is easy enough to fix because I know the total number of results before hand so I can just subtract it from the returned results, but Id rather have the mysql correct.

Perhaps someone knows generally why this happens.
Anyway the code is

select sum(ability_1) as level_1, sum(ability_2) as level_2, sum(ability_3) as level_3, sum(ability_4) as level_4, sum(ability_5) as level_5 from products p left join manufacturers m using(manufacturers_id) left join products_description pd on p.products_id=pd.products_id left join model_information_table mit on mit.model_number=p.products_model where p.products_status = '1' and p.products_quantity>0 and (pd.products_name like '%lightning%' or p.products_model = 'lightning' or p.products_id = 'lightning' or m.manufacturers_name like '%lightning%')
Comment
Watch Question

awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
Can you post the relevant table structures with some sample data?
Web Developer, specialising in WordPress
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Terry,
The result is an array in which many items are duplicated which was causing the summation error.. I cannot figure out what code is missing but adding dinstinct(p.products_id) to the select clause returns a valid array.  THis is ok but then I have to run a summation php loop
            while ($refine_result=tep_db_fetch_array($result)){
                  echo $refine_result['products_id'].'<br/>';
                  $sum_array[1] += $refine_result['ability_1'];
                  $sum_array[2] += $refine_result['ability_2'];                  
                  $sum_array[3] += $refine_result['ability_3'];                  
                  $sum_array[4] += $refine_result['ability_4'];                  
                  $sum_array[5] += $refine_result['ability_5'];
            }      
which isnt as slow as running multiple sqls, but still seems avoidable.. here is a gist of what is going on
there may be muliple p.products_id that have the same p.products_model.. these will all be returned in the select and were part of the original summation (which is correct) however is three products_id results were returned for the same value p.products_model then each of these would actually be returned 3 times in the select clause

I understand the problem exactly (thanks for leading me there) and have a working solution (your code with the added part to select above) but still thing there must be a simple clarification so that the mysql query can returned the sum result as I was originally intending
Terry WoodsWeb Developer, specialising in WordPress
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Which table are the ability_* columns in?

Author

Commented:
model_information_table
Terry WoodsWeb Developer, specialising in WordPress
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Then perhaps this?
select sum(ability_1) as level_1,
       sum(ability_2) as level_2,
       sum(ability_3) as level_3,
       sum(ability_4) as level_4,
       sum(ability_5) as level_5
  from model_information_table mit 
  where exists (select p.* 
                  from products p left join manufacturers m using(manufacturers_id) 
                                  left join products_description pd on p.products_id=pd.products_id 
                  where p.products_model = mit.model_number
                    and p.products_status = '1' 
                    and p.products_quantity>0 
                    and (pd.products_name like '%lightning%' 
                         or p.products_model = 'lightning' 
                         or p.products_id = 'lightning' 
                         or m.manufacturers_name like '%lightning%')
               )

Open in new window

Terry WoodsWeb Developer, specialising in WordPress
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
I helped the asker to understand the issue and get it resolved:
"I understand the problem exactly (thanks for leading me there) and have a working solution (your code with the added part to select above)"
as well as providing an additional solution when they provided further clarification for the problem.

It took substantial effort to go through that process, and I was successful. I deserve the points. Thanks.
Terry WoodsWeb Developer, specialising in WordPress
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
I helped the asker to understand the issue and get it resolved:
"I understand the problem exactly (thanks for leading me there) and have a working solution (your code with the added part to select above)"
as well as providing an additional solution when they provided further clarification for the problem.

It took substantial effort to go through that process, and I was successful. I deserve the points. Thanks.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.