Witam ! guitar.gif
Tabela review [id, numeric(wartość liczbowa), unit_id (jednostka w jakiej została zapisana wartość załóżmy (1-mb,2-kb,3-gb)] jak usprawnić poniższe zapytanie aby brało pod uwagę przelicznik wartości zapisany w innej tabeli np conversion_unit [conv_id,c_src_unit_id,c_value] bo jak dotej pory powstaje mi taki blad ze kilka wierszy zapisanych w mb jest sumowana z kilkoma w kb i mam kaszane

[SQL] pobierz, plaintext 
  1. SELECT count(*) AS weight, SUM(`rv`.`numeric`) AS count, `product_id` AS axis FROM `review_values_numeric` AS rv, `properties`, `review` AS r WHERE `rv`.`properties_id` = `properties`.`property_id` && `rv`.`review_id` = `r`.`id` && `r`.`type` = 'PRODUCT' && `r`.`campaign_id` = '14' && `r`.`date` >= '2008-02-18' && `r`.`date` <= '2008-05-26' && `properties`.`p_field_type_id` = 31 && `properties`.`p_segment_id` IN (100, 101, 102, 103, 103) && `numeric` <> 0 && ( `r`.`product_id`=62 || `r`.`product_id`=64 || `r`.`product_id`=65 || `r`.`product_id`=66 || `r`.`product_id`=67 || `r`.`product_id`=68 || `r`.`product_id`=69 || `r`.`product_id`=70 || `r`.`product_id`=71 || `r`.`product_id`=72 || `r`.`product_id`=73 || `r`.`product_id`=74 || `r`.`product_id`=75 || `r`.`product_id`=76 || `r`.`product_id`=77) GROUP BY `axis`
[SQL] pobierz, plaintext


sprawa rozwiazana mam nadzieje ze sie komus przyda 
[SQL] pobierz, plaintext 
  1. SELECT SUM(`s`.`weight`) AS `weight`,SUM(`s`.`count`) AS `count`, axis FROM ( SELECT count(*) AS `weight`, `rv`.`unit_id`, (SUM(`rv`.`numeric`)*IFNULL((SELECT `conv`.`c_value` FROM `conversion` AS `conv` WHERE `conv`.`c_src_unit_id`=`rv`.`unit_id`),1)) AS `count`, `product_id` AS `axis` FROM `review_values_numeric` AS `rv`, `properties` AS `p`, `review` AS `r` WHERE `rv`.`properties_id` = `p`.`property_id` && `rv`.`review_id` = `r`.`id` && `r`.`type` = 'PRODUCT' && `r`.`campaign_id` = '14' && `r`.`date` >= '2008-02-18' && `r`.`date` <= '2008-07-07' && `p`.`p_field_type_id` = 31 && `p`.`p_segment_id` IN (100, 101, 102, 103, 103) && `numeric` <> 0 && ( `r`.`product_id`=62 || `r`.`product_id`=64 || `r`.`product_id`=65 || `r`.`product_id`=66 || `r`.`product_id`=67 || `r`.`product_id`=68 || `r`.`product_id`=69 || `r`.`product_id`=70 || `r`.`product_id`=71 || `r`.`product_id`=72 || `r`.`product_id`=73 || `r`.`product_id`=74 || `r`.`product_id`=75 || `r`.`product_id`=76 || `r`.`product_id`=77) GROUP BY `unit_id`,`axis`) AS `s` GROUP BY `s`.`axis`
[SQL] pobierz, plaintext