Re: [SQL] Ordering a date_part() query ... - Mailing list pgsql-sql

Had thought about that one:

SELECT ( date_part('month', stat_period) || '/' ||        date_part('day', stat_period)   || '/' ||
date_part('year',stat_period)  || ' ' ||        date_part('hour', stat_period)  || ':00:00' ) as "Summary - by
Day/Hour",     sum(impressions) as impressions, sum(click_thru) as click_thru FROM banner_count
 
GROUP BY "Summary - by Day/Hour"
ORDER BY "Summary - by Day/Hour"::datetime;

results in:

ERROR:  attribute 'Summary - by Day/Hour' not found

But, that gave me an idea...

SELECT ( date_part('month', stat_period) || '/' ||        date_part('day', stat_period)   || '/' ||
date_part('year',stat_period)  || ' ' ||        date_part('hour', stat_period)  || ':00:00' )::datetime as day,
sum(impressions)as impressions, sum(click_thru) as click_thru FROM banner_count
 
GROUP BY day;


CASTing the results gives me what I want, and gives cleaner lookign
results then what I had :)


Summary - by Day/Hour       |impressions|click_thru
----------------------------+-----------+----------
Mon Jan 17 23:00:00 2000 EST|         63|         1
Tue Jan 18 00:00:00 2000 EST|        151|         0
Tue Jan 18 01:00:00 2000 EST|         89|         0
Tue Jan 18 02:00:00 2000 EST|        112|         0
Tue Jan 18 03:00:00 2000 EST|        178|         0
Tue Jan 18 04:00:00 2000 EST|        184|         0
Tue Jan 18 05:00:00 2000 EST|        133|         0
Tue Jan 18 06:00:00 2000 EST|        119|         0
Tue Jan 18 07:00:00 2000 EST|        148|         0
Tue Jan 18 08:00:00 2000 EST|        224|         0
Tue Jan 18 09:00:00 2000 EST|        272|         0
Tue Jan 18 10:00:00 2000 EST|        348|         4
Tue Jan 18 11:00:00 2000 EST|        804|         1
Tue Jan 18 12:00:00 2000 EST|        632|         1
Tue Jan 18 13:00:00 2000 EST|       1200|         0
Tue Jan 18 14:00:00 2000 EST|        110|         0


On Tue, 18 Jan 2000, Mark Volpe wrote:

> INSERT INTO my_brain VALUES ('clue') :-)
> 
> Whoops, Bruce's response reminded me,
> what I meant to say was
> ORDER BY "Summary - by Day/Hour"::datetime
> 
> Mark
> 
> The Hermit Hacker wrote:
> > 
> > I have a query that looks like:
> > 
> > SELECT ( date_part('month', stat_period) || '/' ||
> >          date_part('day', stat_period)   || '/' ||
> >          date_part('year', stat_period)  || ' ' ||
> >          date_part('hour', stat_period)  || ':00:00' ) as "Summary - by
> > Day/Hour",
> >        sum(impressions) as impressions, sum(click_thru) as click_thru
> >   FROM banner_count
> > GROUP BY "Summary - by Day/Hour";
> >
> 

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org