From: | Joseph Shraibman <jks(at)selectacast(dot)net> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: sorting by day of the week |
Date: | 2006-01-25 02:39:11 |
Message-ID: | dr6oc8$1475$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Nevermind, I figured out that I just needed to do it like this:
SELECT to_char( logtime, 'Dy'),count(*),to_char( logtime, 'D') FROM
sclog WHERE date_trunc('day', logtime) > current_date + '7 day
ago'::interval group by to_char( logtime, 'Dy'),to_char( logtime, 'D')
ORDER BY to_char( logtime, 'D') DESC;
It is interesting that I can't put to_char( logtime, 'D') in the the
group by without putting it in the select.
Joseph Shraibman wrote:
> p8:owl=>SELECT to_char( logtime, 'Dy'),count(*) FROM logtab WHERE
> date_trunc('day', logtime) > current_date + '7 day ago'::interval group
> by to_char( logtime, 'Dy') ORDER BY to_char( logtime, 'Dy') DESC;
> to_char | count
> ---------+-------
> Wed | 1447
> Tue | 618
> Thu | 1161
> Sun | 230
> Sat | 362
> Mon | 760
> Fri | 1281
> (7 rows)
>
> The problem is that I want those results sorted in day of week order,
> not text order of the day name, so I tried this:
>
> p8:owl=>SELECT to_char( logtime, 'Dy'),count(*) FROM sclog WHERE
> date_trunc('day', logtime) > current_date + '7 day ago'::interval group
> by to_char( logtime, 'Dy') ORDER BY to_char( logtime, 'D') DESC;
> ERROR: column "sclog.logtime" must appear in the GROUP BY clause or be
> used in an aggregate function
>
> Now obviously I don't want to group by logtime (a timestamp) so how do I
> work around this? What I really need is a function that converts from
> the char representation to a day of week number or vice versa. I also
> have the same problem with month names.
From | Date | Subject | |
---|---|---|---|
Next Message | ipv | 2006-01-25 03:01:07 | Fw: stored procedures for complex SELECTs |
Previous Message | Joseph Shraibman | 2006-01-25 01:23:50 | sorting by day of the week |