SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: COUNT times 2!

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Dec 2009
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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!


    Thanks

    Oldnickj

    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)
    FROM members
    INNER
    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

  2. #2
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  3. #3
    SitePoint Member
    Join Date
    Dec 2009
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, that did the trick, your the best!

    Oldnickj

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •