Values in map don't match values in Stats

[clhenrick]

Via Christine on 2/2/2017

When hovering over each dot I add up approximately 40 fatalities versus the 124 shown in the summary

[clhenrick]

This likely due to the fact that the map sql query has to select geometries that aren't null, while the stats sql query selects all rows regardless of whether or not it has a geometry.

Example stats query:

--- filter where pedestrians_killed > 0
SELECT COUNT(c.cartodb_id) as total_crashes, 
SUM(CASE WHEN c.number_of_persons_injured > 0 THEN 1 ELSE 0 END) AS total_crashes_with_injury, 
SUM(CASE WHEN c.number_of_persons_killed > 0 THEN 1 ELSE 0 END) AS total_crashes_with_death, 
SUM(c.number_of_cyclist_injured) as cyclist_injured, 
SUM(c.number_of_cyclist_killed) as cyclist_killed, 
SUM(c.number_of_motorist_injured) as motorist_injured, 
SUM(c.number_of_motorist_killed) as motorist_killed, 
SUM(c.number_of_pedestrian_injured) as pedestrian_injured, 
SUM(c.number_of_pedestrian_killed) as pedestrian_killed, 
SUM(c.number_of_persons_injured) as persons_injured, 
SUM(c.number_of_persons_killed) as persons_killed 
FROM export2016_07 c  
WHERE (date_val <= date 'Aug 6, 2016') 
AND (date_val >= date 'Jul 1, 2016')  
AND ( number_of_pedestrian_killed > 0 )

Example map query:

--- filter where pedestrians > 0
SELECT c.the_geom, 
c.the_geom_webmercator, 
c.on_street_name, 
c.cross_street_name, 
COUNT(c.cartodb_id) as total_crashes, 
SUM(c.number_of_cyclist_injured) as cyclist_injured, 
SUM(c.number_of_cyclist_killed) as cyclist_killed, 
SUM(c.number_of_motorist_injured) as motorist_injured, 
SUM(c.number_of_motorist_killed) as motorist_killed, 
SUM(c.number_of_pedestrian_injured) as pedestrian_injured, 
SUM(c.number_of_pedestrian_killed) as pedestrian_killed, 
SUM(c.number_of_pedestrian_injured + c.number_of_cyclist_injured + c.number_of_motorist_injured) as persons_injured, 
SUM(c.number_of_pedestrian_killed + c.number_of_cyclist_killed + c.number_of_motorist_killed) as persons_killed 
FROM export2016_07 c  
WHERE (date_val <= date '2016-08-06') 
AND (date_val >= date '2016-07-01')  
AND ( number_of_pedestrian_killed > 0 )  
AND c.the_geom IS NOT NULL 
GROUP BY c.the_geom, c.the_geom_webmercator, c.on_street_name, c.cross_street_name

[clhenrick]

Should explain this in the "about" or "about the data" section though as to not confuse users.