mysql sum retrieving expected results+total results
Posted on 2009-06-30
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%')