# Thread: Multiple counts from a table

1. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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?

3. Registered User
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. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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?

6. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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

8. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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

10. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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

12. Registered User
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. SQL Consultant
Join Date
Apr 2002
Location
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")

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

Thanks
Nick

15. SQL Consultant
Join Date
Apr 2002
Location