Обсуждение: 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
>
>