COUNT times 2!
The following query is doubling the count of "cases" in the patients table. The "doc" in the members table has only one case but the query is reporting 2!
SELECT members.mfirst , members.mmiddle , members.mlast , members.id , members.member_id , label.descr_lbl , EXTRACT(YEAR
FROM patients.cdate) AS Year , EXTRACT(MONTH FROM patients.cdate) AS yyyymm , COUNT(patients.doc_id)
JOIN label ON label.value_lbl = members.demo5
INNER JOIN patients ON patients.doc_id = members.id AND members.mlast ='jewett'
GROUP BY members.id , yyyymm
ORDER BY mlast, patients.cdate
the patients table has 35k records
the members table has 500
the label table just 54
You probably have two labels for that doc, which results in two rows being generated for the same case in the results.
Try changing COUNT(patients.doc_id) to COUNT(DISTINCT patients.doc_id), so that it will only count the number of different doc_id values returned by the query.
Thanks, that did the trick, your the best!
using COUNT DISTINCT instead of COUNT often just masks an underlying problem
you should really be asking yourself why does a given member have 2 labels?
also, notice something else of important relevance here
because of mysql's quirky, non-standard grouping behaviour, it is possible to have a column in the SELECT clause that's "hidden" from the GROUP BY clause
in this case, you're grouping on the member but not on the label, which is on the SELECT clause
consequently, if a member does actually have more than one label, the value that's selected will be indeterminate
therefore you should have something like MIN(label.descr_lbl) or MAX(label.descr_lbl) instead of just label.descr_lbl in the SELECT clause
or better yet, fix the data so that each member has just one label