title: Querying the monthly average population
body: <p>I have a table called <code>patients</code>. I'm trying to query the Monthly Average Daily Patients. For example, if I query today:</p>
  
  <p><code>select count(*) from patients where active=1</code></p>
  
  <p>It returns:</p>
  
  <pre><code>-----------
  213
  
  (1 row(s) affected)
  </code></pre>
  
  <p>Mathematically, I would need to get the count per day, then divide by the number of days in the month.</p>
  
  <p>If I want to get the average daily count of active patients per month, how would I do this? </p>
  
  <p>edit:</p>
  
  <p>Some sample data showing a <code>Patient ID</code> and their <code>creation_date</code>.</p>
  
  <pre><code>Patient ID  creation_date
  ----------- -----------------------
  48          2011-11-16 08:59:34.000
  55          2011-11-16 09:09:20.000
  82          2011-11-16 09:32:48.000
  110         2011-11-16 09:42:38.000
  111         2011-11-16 09:42:53.000
  123         2011-11-16 09:47:01.000
  138         2011-11-16 09:58:02.000
  188         2011-11-16 10:20:03.000
  225         2011-11-16 10:32:53.000
  231         2011-11-16 10:34:48.000
  241         2011-11-16 10:38:13.000
  259         2011-11-16 10:44:35.000
  377         2011-12-17 10:26:21.000
  536         2012-02-02 16:10:57.000
  551         2012-02-05 11:42:22.000
  591         2012-02-12 12:14:57.000
  </code></pre>
  
  <p>My desired results would be something like:</p>
  
  <pre><code>Month                          Month Number Year        ADP
  ------------------------------ ------------ ----------- -----------
  November                       11           2011        240
  December                       12           2011        280
  January                        1            2012        220
  February                       2            2012        225
  March                          3            2012        241
  April                          4            2012        212
  May                            5            2012        210
  </code></pre>
  
  <p>EDIT: it seems like the answers submitted are giving me an average daily count for NEW patients, which is a smaller number. I need the average daily count for the TOTAL population.</p>
  
  <p>EDIT: I've learned that patient status tracking are tracked in a table called <code>patient_booking_data</code> with a column called <code>release</code>. This might help. Below is sample data from this table:</p>
  
  <pre><code>id          pid         booking_no                       date                    release                 active facility date_created            temporary temporary_no
  ----------- ----------- -------------------------------- ----------------------- ----------------------- ------ -------- ----------------------- --------- --------------------------------
  1           1           12345                            2011-11-03 00:00:00.000 2011-11-15 10:45:00.000 0      11535    2011-11-03 12:45:36.000 0         NULL
  2           2           7890                             2011-11-14 12:00:00.000 2011-11-21 07:01:00.000 1      11535    2011-11-14 08:45:33.000 0         NULL
  3           3           100                              2011-11-14 09:00:00.000 2011-11-21 07:00:00.000 1      11535    2011-11-14 08:45:34.000 0         NULL
  4           4           111                              2011-11-14 09:00:00.000 2011-11-21 07:01:00.000 1      11535    2011-11-14 08:45:34.000 0         NULL
  5           5           12                               2011-11-14 10:20:00.000 2011-11-21 07:02:00.000 1      11535    2011-11-14 10:21:25.000 0         NULL
  6           6           1234                             2011-11-14 00:00:00.000 2011-11-21 07:02:00.000 1      11535    2011-11-14 10:25:10.000 0         NULL
  7           7           1123                             2011-11-14 11:14:00.000 2011-11-21 07:01:00.000 1      11535    2011-11-14 11:15:44.000 0         NULL
  </code></pre>