Results 1 to 15 of 15
  1. #1
    Join Date
    Jan 2009
    Posts
    124

    Unanswered: Multiple counts from a table

    This query works. would like to get a count of the previous 30 days (30-60 days ago). Secondly how to compute the average patients per month.

    Nick

    SELECT COUNT(patients.doc_id),members.mfirst, members.mmiddle, members.mlast, members.id,
    members.member_id, label.descr_lbl
    FROM (members
    LEFT JOIN label ON label.value_lbl=members.demo5)
    LEFT JOIN patients ON members.id = patients.doc_id
    WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= patients.cdate
    GROUP BY patients.doc_id

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    we need to cover a couple of concepts before working out the solution

    first is why the LEFT OUTER JOINs? do you know the difference between x INNER JOIN y and x LEFT OUTER JOIN y?

    second is table relationship cardinalities -- one-to-one or one-to-many

    (the special case of many-to-many, as you've seen, is handled by two one-to-many relationships with an intervening relationship or association table)

    so what are the cardinalities of your relationships between the three tables?

    and out of curiosity, what's a label and why is there a table for it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2009
    Posts
    124
    I assumed an outer join because each doctor has many patients but some doctors have not entered patients so we list them as not having patients. I want all the members and the patient counts for those members who have entered patients.

    members stores a value which and I pick up the corresponding description from the label table.

  4. #4
    Join Date
    Jan 2009
    Posts
    124
    Page 39! What I haven't grasped yet is the best way to do multiple counts on the same table.

    Nick

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so members are doctors? okay

    and labels are fine, provided you don't go overboard with this concept and decide to put all descriptions in there -- do a search for One True Lookup Table (OTLT)

    finally, you seem to be counting only one thing (the number of patients since 30 days ago) -- what multiple counts were you hoping to make?

    here's my attempt to fix your query --
    Code:
    SELECT members.mfirst
         , members.mmiddle
         , members.mlast
         , members.id
         , members.member_id
         , label.descr_lbl
         , COUNT(patients.doc_id)
      FROM members
    INNER
      JOIN label 
        ON label.value_lbl = members.demo5
    LEFT OUTER
      JOIN patients 
        ON patients.doc_id = members.id 
       AND patients.cdate >= CURRENT_DATE - INTERVAL 30 DAY
    GROUP 
        BY members.id
    i changed the GROUP BY column but i'm not sure that's the right column -- what is the difference between members.id and members.member_id? why do you need both?

    notice INNER JOIN for the label -- it is unlikely you would assign a demo5 value for a label that doesn't exist right? (although it is still possible, unless you use a foreign key for it)

    make sure you (eventually) understand why the date condition must go into the ON clause, rather than the WHERE clause

    and page 39 refers to the INNER JOIN section in Simply SQL?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2009
    Posts
    124
    Members.id ids the primary key which is different from the Member.member_id which is the clients Association ID Number and I don't trust it enough to use as my primary.

    What I need it know how many patients each doctor added in the last 60 days as well as the last 30, then it would be nice to know the average patients added per month.

    Yes, p.39 is from the Simply SQL book which is at the top of my pile of SQL books!

    Nick

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT members.mfirst
         , members.mmiddle
         , members.mlast
         , members.id
         , members.member_id
         , label.descr_lbl
         , COUNT(
             CASE WHEN patients.cdate >= CURRENT_DATE - INTERVAL 60 DAY
                  THEN 'Y' ELSE NULL END  
                ) AS last_60_days
         , COUNT(
             CASE WHEN patients.cdate >= CURRENT_DATE - INTERVAL 30 DAY
                  THEN 'Y' ELSE NULL END  
                ) AS last_30_days
      FROM members
    INNER
      JOIN label 
        ON label.value_lbl = members.demo5
    LEFT OUTER
      JOIN patients 
        ON patients.doc_id = members.id 
       AND patients.cdate >= CURRENT_DATE - INTERVAL 60 DAY
    GROUP 
        BY members.id
    average patients per month is trickier and will depend on whether you mean calendar months or just these 30/60 day counts, in which case just divide last_60_days by 2 and last_30_days by 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2009
    Posts
    124
    Great! Since the count is done in the Select statement does the line "AND patients.cdate >= CURRENT_DATE - INTERVAL 60 DAY" do anything?

    NIck

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, it does

    good of you to spot that, it shows you are learning fast

    the purpose is to restrict the counting to only those rows of interest

    without that condition, the LEFT OUTER JOIN will retrieve ~all~ rows from the patients table, including all older than 60 days

    the counts will still come out as 0 for these extra rows, but you've retrieved them needlessly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jan 2009
    Posts
    124
    Morning,
    Since this does not use the BETWEEN function how do I query for 1-30 days past, 30-60 days past and 60-90 days?

    Nick

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    have a look at the ON clause -- you can see there where the date is being filtered in one line

    if you want a specific range, you'd use two lines, e.g.
    Code:
       AND patients.cdate >= CURRENT_DATE - INTERVAL 60 DAY
       AND patients.cdate  < CURRENT_DATE - INTERVAL 30 DAY
    there's your "between" concept for 30-60 days old

    then you'd need to adjust your SELECT clause expressions accodingly
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jan 2009
    Posts
    124
    My client responded "The report should NOT be 30, 60, 90 day totals. It should write a single report that provides MONTHLY totals by surgeon (CBA_ID) from the time they start data entry."

    I'm unsure as to work in terns of months vs. ranges of 30 days at a time. I wold need to the first date of entering a case add a month count cases then go to the next month. In any case I would guess the query would have to work on one doctor at a time as each doctor starts entering at different dates.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh sure, change the specs why don'tcha...
    Code:
    SELECT members.mfirst
         , members.mmiddle
         , members.mlast
         , members.id
         , members.member_id
         , label.descr_lbl
         , EXTRACT(YEAR_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 
    GROUP 
        BY members.id
         , yyyymm
    note that the join has been changed to INNER, because obviously 0 counts are not wanted ("... from the time they start data entry")

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2009
    Posts
    124
    Great! Lastly , how about formating the date i.e 2009 - 2

    Thanks
    Nick

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can't do that in your front end application??
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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