From: | Stuart Rison <stuart(at)ludwig(dot)ucl(dot)ac(dot)uk> |
---|---|
To: | Kevin Heflin <kheflin(at)shreve(dot)net>, "PGSQL-General (E-mail)" <pgsql-general(at)postgreSQL(dot)org> |
Subject: | Re: [GENERAL] looking for suggestions |
Date: | 1999-08-24 08:39:09 |
Message-ID: | v04020a02b3e80816b232@[128.40.242.190] |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 |
+--------------------------+--------------------------------------+
From | Date | Subject | |
---|---|---|---|
Next Message | Wim Kerkhoff | 1999-08-24 09:15:29 | Re: [GENERAL] psql dumping |
Previous Message | amy cheng | 1999-08-24 07:49:11 | how powerful is PL/pgSQL |