Thread: sorting by day of the week
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.
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.
Hi, Try to use a calculated index: (tested similar solution, but not this code) CREATE OR REPLACE FUNCTION TestOrder (nameTable.weekDay%TYPE) RETURNS INT AS ' DECLARE numWeekDay INT; BEGIN if ($1 = ''Wed'') then numWeekDay := 1; if ($1 = ''Tue'') then numWeekDay := 2; ..... RETURN (numWeekDay); END; ' LANGUAGE 'plpgsql' STRICT IMMUTABLE; CREATE INDEX idx_TestOrder ON nameTable USING btree (TestOrder(nameTable.weekDay)); SELECT * FROM trajecte ORDER BY TestOrder(nameTable.weekDay); Regards ----- Original Message ----- From: "Joseph Shraibman" <jks@selectacast.net> To: <pgsql-sql@postgresql.org> Sent: Wednesday, January 25, 2006 2:23 AM Subject: [SQL] sorting by day of the week > 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. > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > >