From: | Joseph Shraibman <jks(at)selectacast(dot)net> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | sorting by day of the week |
Date: | 2006-01-25 01:23:50 |
Message-ID: | dr6jv0$emi$2@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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 | Joseph Shraibman | 2006-01-25 02:39:11 | Re: sorting by day of the week |
Previous Message | Bath, David | 2006-01-25 01:05:09 | Re: How to implement Microsoft Access boolean (YESNO) |