Search code examples
mysqlsqlmariasql

Count values off when grouping by year, month


I'm having a problem with my mySQL Statements. Statement 1 will give me the correct answer however, when I scale it into Statement 2 I'm getting count values that are significantly off. Am I missing a fundamental issue with GROUP BY?

What I really need is to return the max 3 counts and the month and year that it was accomplished. Is there a solution? Thanks in advance!

edit: using sql mode = full_group_by

SELECT
  count(t2.rec_date), any_value(t2.rec_date)
FROM results t1
  join patients t2
  on t1.barcode = t2.barcode
WHERE test LIKE '%ssc%'
and year(rec_date) = 2017
and month(rec_date) = 2;
>>> 366, '2017-02-01'

SELECT
  count(t2.rec_date) AS count,
  any_value(rec_date)
FROM results t1
  join patients t2
  on t1.barcode = t2.barcode
WHERE test LIKE '%ssc%'
GROUP BY EXTRACT(YEAR_MONTH FROM run_date)
ORDER BY count DESC
LIMIT 3;
>>> 415, 2017-02-28
>>> 373, 2017-01-31
>>> 365, 2016-10-04

Solution

  • I think this is one of those horrid cases where you simply need a fresh pair of eyes to look at your code.

    The first query filters based on the year and month from the field rec_date.

    The second query groups by the year and month from the field run_date.

    Unless these fields are tied together somehow, I think this must explain the difference in the counts.