CODEWITHSUNDEEP
sqlselectcount
LaneWalker
LaneWalker

Reputation: 255

I need to use a COUNT operation in Access SQL

I have the following tables:

   PATIENT                PRACTICE
- PATIENT_ID            - PRACTICE_ID
- PATIENT_NAME          - PRACTICE_NAME
- PRACTICE_ID

These tables keep track of patients who attend medical practices. I need to use a COUNT operation to count the number of patients that each practice has, then order the results by ascending order of number of patients.

The following is the desired output:

PracticeName        NumberOfPatients
North Medical       3
East Medical        4
South Medical       5
West Medical        6

I have tried this so far, but it doesn't count it as I expected:

SELECT BRANCH.BRANCH_NAME, COUNT(EMPLOYEE.EMP_NUM) 
FROM EMPLOYEE, BRANCH
GROUP BY BRANCH.BRANCH_NAME, EMPLOYEE.EMP_NUM;

Upvotes: 0

Views: 72

Answers (2)

Michael Kersten
Michael Kersten

Reputation: 427

You seem to be rather new to SQL, so your question is a little bit fuzzy. Try this:

select pra.practice_name,
       count(pat.patient_id) as NumberOfPatients
from patient as pat inner join practice as pra on (pat.practice_id = pra.practice_id)
group by pra.practice_name

And learn the basics of joining tables to get a better understanding of SQL.

Upvotes: 0

tom.dietrich
tom.dietrich

Reputation: 8347

This is a pretty trivial question and you should have done some rudimentary research before asking it, which is why someone voted your question down.

For the sake of helping the new guy out, here is a query that will give you those results.

 SELECT pr.Practice_Name, COUNT(pa.Patent_ID) as NumberOfPatients
 FROM practice pr INNER JOIN patient pa ON pr.practice_id = pa.practice_id 
 GROUP BY pr.practice_name 
 ORDER BY COUNT(pa.patientid)

Upvotes: 1

Related Questions