Thread

1. looking for suggestions

   Kevin Heflin <kheflin@shreve.net> — 1999-08-24T02:10:10Z

   I've got a single table which lists usernames, addressinfo and signup
   dates
   
   signup date is data type 'date'
   
   I'm wondering if it's possible through the magic of SQL to run a query
   like:
   
   select * from table where signupdate > '01/01/1999' and < '04/01/1999';
   
   then in the results, I would like to break this down by months. So I can
   show total signups for january 1999, then February 1999 etc..
   
   
   Kevin
   
   
   
   
   
   --------------------------------------------------------------------
   Kevin Heflin          | ShreveNet, Inc.      | Ph:318.222.2638 x103
   VP/Mac Tech           | 333 Texas St #175    | FAX:318.221.6612
   kheflin@shreve.net    | Shreveport, LA 71101 | http://www.shreve.net
   --------------------------------------------------------------------
   
   
   

2. Re: [GENERAL] looking for suggestions

   Stuart Rison <stuart@ludwig.ucl.ac.uk> — 1999-08-24T08:39:09Z

   At 9:10 pm -0500 23/8/99, Kevin Heflin wrote:
   >I've got a single table which lists usernames, addressinfo and signup
   >dates
   >
   >signup date is data type 'date'
   >
   >I'm wondering if it's possible through the magic of SQL to run a query
   >like:
   >
   >select * from table where signupdate > '01/01/1999' and < '04/01/1999';
   
   Hi Kevin,
   
   Two ways to define a range:
   
   WHERE signupdate > '01/01/1999'
   AND signupdate < '04/01/1999';
   
   or using syntaxtic sugar:
   
   WHERE signupdate BETWEEN '01/01/1999' AND '04/01/1999';
   
   >then in the results, I would like to break this down by months. So I can
   >show total signups for january 1999, then February 1999 etc..
   
   Try something like the following:
   
   SELECT date_part('month',date_report) AS month,
   date_part('year',date_report) AS year, count(*) AS sign_ups
   FROM path_reports
   WHERE date_report BETWEEN '01/01/1950' and '01/01/2000'
   GROUP BY by 1,2 -- group by year and month
   ORDER BY 3; -- order by if you want a different order then implied by group
   in this instance we sort by number of signups
   
   HTH,
   
   S.
   
   
   
   
   +--------------------------+--------------------------------------+
   | Stuart C. G. Rison       | Ludwig Institute for Cancer Research |
   +--------------------------+ 91 Riding House Street               |
   | N.B. new phone code!!    | London, W1P 8BT                      |
   | Tel. +44 (0)207 878 4041 | UNITED KINGDOM                       |
   | Fax. +44 (0)207 878 4040 | stuart@ludwig.ucl.ac.uk              |
   +--------------------------+--------------------------------------+