DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: selecting row counts

  1. #1
    Join Date
    Mar 2005
    Posts
    143

    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.
    so
    Code:
    select count(1) from appointment where patient=1;
    i get 3
    Code:
    select count(1) from appointment where patient=2;
    i get one
    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
    patient num
    1 3
    2 1
    I am not sure how to do this. Any help would be appreciated.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    You are asking about and aggregate query:

    Code:
    SELECT patient, COUNT(*)
      FROM appointment
     GROUP BY patient
    ORDER BY 1;

Posting Permissions

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


Click Here to Expand Forum to Full Width