>So that in all cases, "value" contains the value of stats.quantity when >the selected month shows up in the iteration loop, and Zero >when any other month shows up, so Summing the values (or indeed doing any >other kind of aggregate function on it) will only affect >stats.quantity values for the specific months.
This will only work for SUM aggregates where n (the count of values) does not affect the outcome. For other aggregates (such as the built-in AVG), you will get incorrect results using: AVG(x * (y = 1)) because you will be adding "extra zero values" into the aggregate that ought not be there. This applies to any aggregate function in which the number of observations is significant (average, standard deviation, variance, kurtosis, etc). For these kinds of aggregates you will have to ensure that NULL is passed into (and ignored by) the aggregate function when the condition is not met: AVG(CASE WHEN y = 1 THEN x ELSE NULL END) _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
