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

On Tue, 18 Jan 2000, Tom Lane wrote:

> The Hermit Hacker <scrappy(at)hub(dot)org> writes:
> > 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;
>
>
> Uh, why don't you just GROUP BY and ORDER BY stat_period?

Okay, maybe I'm misunderstanding GROUP BY, but my understanding is that it
groups "like" values ... my above example would give me a count of all
impressions received over a one hour period ...

To use stat_period, I'd get one row for each 'stat_period' value, which,
since its in seconds, would mean every record in the table...

The above takes off the min/secs and then I group by hours ...

> I also wonder whether you can't find a combination of date_trunc and
> datestyle that will produce the output format you want. Doing it
> like the above sure seems like using the wrong tool for the job...

It is quite possible ... I'm not used to using functions much so far,
first time I've really had a use for it...

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