Trying to populate a specific column, based on the sum...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
alturic
Forum Newbie
Posts: 2
Joined: Sat Aug 20, 2016 7:43 am

Trying to populate a specific column, based on the sum...

Post by alturic »

I have the query below which returns all of the columns I am expecting. However, the date_met column is seeming to return a.) the same date for all results and b.) the wrong date for all results. It looks like it's really just picking the most current date in the date_start column, considering it's putting 2016-11-06, which IS a valid date_start, but it's outside of the main queries BETWEEN...

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`
As I said, it's returning all of the columns I expect, I just can't get it to put the correct dateMet when the SUM(certifications.total_snow) >= the_thresholds.threshold_snow was reached.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Trying to populate a specific column, based on the sum..

Post by Christopher »

dateMet will always be the same, either: MIN(certifications.date_start) or 0 depending on the condition IF(SUM(certifications.total_snow) >= the_thresholds.threshold_snow)
(#10850)
Post Reply