Re: [SQL] Ordering a date_part() query ...

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(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org