Multiple counts from a table

oldnickj · #1 (**permalink**) 02-11-09, 01:47

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

r937 · #2 (**permalink**) 02-11-09, 05:11

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?

oldnickj · #3 (**permalink**) 02-11-09, 10:05

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.

oldnickj · #4 (**permalink**) 02-11-09, 10:43

Page 39! What I haven't grasped yet is the best way to do multiple counts on the same table.

Nick

r937 · #5 (**permalink**) 02-11-09, 11:04

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?

oldnickj · #6 (**permalink**) 02-11-09, 12:21

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

r937 · #7 (**permalink**) 02-11-09, 12:28

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

oldnickj · #8 (**permalink**) 02-11-09, 13:46

Great! Since the count is done in the Select statement does the line "AND patients.cdate >= CURRENT_DATE - INTERVAL 60 DAY" do anything?

NIck

r937 · #9 (**permalink**) 02-11-09, 14:19

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

oldnickj · #10 (**permalink**) 02-17-09, 12:56

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

r937 · #11 (**permalink**) 02-17-09, 13:15

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

oldnickj · #12 (**permalink**) 02-18-09, 12:00

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.

r937 · #13 (**permalink**) 02-18-09, 12:16

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")