Help - I'm not sure if this is a bug, but I wouldn't call it a feature! :-)
Here are my attempts at selecting out the counts for the number of records for
each particular day of the week. I'd like to be able to show the day of the
week sorted in the order of the days in the week. As you can see, I can select
out the information and print it in non-sorted order and I can sort it as
desired if I use the number of the day of the week, but I can't seem to print
it sorted as desired with the day (eg., MON, TUE,...) shown.
psql=# SELECT to_char(mytimestamp,'DY'),COUNT(*) FROM mytable GROUP BY
to_char(mytimestamp,'DY') ORDER BY to_char(mytimestamp,'DY');
to_char | count
---------+-------
FRI | 21
MON | 23
SAT | 23
SUN | 25
THU | 22
TUE | 22
WED | 22
(7 rows)
psql=# SELECT to_char(mytimestamp,'DY'),COUNT(*) FROM mytable GROUP BY
to_char(mytimestamp,'DY') ORDER BY to_char(mytimestamp,'D');
ERROR: column "mytable.mytimestamp" must appear in the GROUP BY clause or be
used in an aggregate function
psql=# SELECT to_char(mytimestamp,'DY'),COUNT(*) FROM mytable GROUP BY
to_char(mytimestamp,'D') ORDER BY to_char(mytimestamp,'D');
ERROR: column "mytable.mytimestamp" must appear in the GROUP BY clause or be
used in an aggregate function
psql=# SELECT to_char(mytimestamp,'D'),COUNT(*) FROM mytable GROUP BY
to_char(mytimestamp,'D') ORDER BY to_char(mytimestamp,'D');
to_char | count
---------+-------
1 | 25
2 | 23
3 | 22
4 | 22
5 | 22
6 | 21
7 | 23
(7 rows)
psql=# SELECT to_char(mytimestamp,'D'),to_char(mytimestamp,'DY'),COUNT(*) FROM
mytable GROUP BY to_char(mytimestamp,'D') ORDER BY to_char(mytimestamp,'D');
ERROR: column "mytable.mytimestamp" must appear in the GROUP BY clause or be
used in an aggregate function
psql=#
Thanks for your help,
Celia McInnis