I am utterly terrible at sub-queries (which I'm pretty sure I do technically need), but I'm unsure where exactly I'm going wrong:
Code: Select all
SELECT
the_thresholds.client_id,
the_thresholds.zipcode,
ziplatlongs.county,
ziplatlongs.city,
ziplatlongs.stateabbr,
the_thresholds.email,
the_thresholds.threshold_snow AS snow_threshold,
SUM(certifications.total_snow) AS rollingTotal,
SUM(certifications.total_snow) >= the_thresholds.threshold_snow AS targetMet,
IF(SUM(certifications.total_snow) >= the_thresholds.threshold_snow,
MIN(certifications.date_start),
0) AS dateMet
FROM
`customer_settings`.`snowfall_thresholds` AS the_thresholds
INNER JOIN
`snowfall_certification`.`the_certifications` AS certifications ON `the_thresholds`.`zipcode` = `certifications`.`zipcode`
INNER JOIN
`zipcodes`.`ziplatlongs` AS ziplatlongs ON `the_thresholds`.`zipcode` = `ziplatlongs`.`zipcode`
WHERE
DATE(`certifications`.`date_start`) BETWEEN '2015-10-01' AND '2016-05-31'
GROUP BY `the_thresholds`.`zipcode`