Mat wrote:
> Hi,
> I have a table with two column, date and data
> I would like to do a set of queries to generate statistics on the data,
> such as count(data) for month blocks and year blocks. What is the best
> way to accomplish this?
>
>
> dd/mm/yy
> date | data
> ---------------
> 01/01/01| 123
> 01/01/01| abc
> 02/01/01| def
> 03/03/01| hij
>
> SOME QUERY ....
>
> Year | Count
> -------------
> 01 | 3
>
> I can see how to group by day - but how do i go about decreasing the
> precision down to months/years.
SELECT COUNT(*)
FROM mytable
GROUP BY date_trunc('month', date);
See:
http://www.postgresql.org/docs/7.3/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
for details.
Hope that helps,
Mike Mascari
mascarm@mascari.com
