Re: Group by date

On 21 Aug 2001 23:10:08 -0700, Dale Emmons <dale@emmons.com> wrote:
> Hello!
>
> I'm trying to be a convert from mysql, which I feel quite comfortable with.
>
> In mysql I can do the following:
>
> CREATE TEMP TABLE hits (
>      datetime TIMESTAMP
> );
>
> Fill it with some data, and then do this:
>
> SELECT datetime, COUNT(*)
> FROM hits
> GROUP BY DATE(datetime)
>
> When I try that in PostgreSQL I get this error when I try the above query:
> Attribute hits.datetime must be GROUPed or used in an aggregate function
>
> The query works fine in mysql, why not postgres?
>

I am not sure what output you want from this query...

You say you want datetime and count(), but it seems more likely what
you want is date(datetime) and count().

Try this:

SELECT DATE(datetime), count(*)
FROM hits
GROUP BY DATE(datetime);

The reason it does not work is that the way you have it, there is
no way to know _which_ datetime should be shown with the count.

In other words, once you GROUP, you will only get one row of output
for each group.

If what you want is each specific datetime and the count of
entries that are on that day, you can create a view that
encapsulates the above query:

create view datecount as
SELECT DATE(datetime), count(*)
FROM hits
GROUP BY DATE(datetime);

and then use that view to help get the data you want:

SELECT datetime, count
FROM hits, datecount
WHERE DATE(datecount)=date;

There is probably some way to do this in one query, but this
is a way I found that works ;)