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
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.