Re: [GENERAL] looking for suggestions

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(at)ludwig(dot)ucl(dot)ac(dot)uk |
+--------------------------+--------------------------------------+