selecting row counts
I want to select a row count but not a total row count for a table. For example I have a table called appointment. Every time a patient has an appointment there is a row in the table for that appointment. So if patient #1 has 3 appointments there are 3 rows for him, and if patient #2 has one appointment there is one row.
i get 3
select count(1) from appointment where patient=1;
i get one
select count(1) from appointment where patient=2;
I want to insert into another table appoint_num that has 2 fields patient and count. I want to insert the patient and the number of rows for each patient so I could run select * from appoint_num and get
I am not sure how to do this. Any help would be appreciated.
You are asking about and aggregate query:
SELECT patient, COUNT(*)
GROUP BY patient
ORDER BY 1;