Re: [GENERAL] Grouping by date range

2003-08-20 Thread Tom Lane 
Bruno Wolff III [EMAIL PROTECTED] writes:
 On Wed, Aug 20, 2003 at 13:44:59 -0500,
   Ron Johnson [EMAIL PROTECTED] wrote:
 The GROUP BY does implicit sorting, so an ORDER BY on the exact same
 column(s) as the GROUP BY is redundant.

 That is an implementation detail, not a promise. With hashed aggregates
 in 7.4, you might find this isn't true.

s/might/will/

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] Grouping by date range

2003-08-20 Thread Ron Johnson 
On Wed, 2003-08-20 at 14:51, Tom Lane wrote:
 Bruno Wolff III [EMAIL PROTECTED] writes:
  On Wed, Aug 20, 2003 at 13:44:59 -0500,
Ron Johnson [EMAIL PROTECTED] wrote:
  The GROUP BY does implicit sorting, so an ORDER BY on the exact same
  column(s) as the GROUP BY is redundant.
 
  That is an implementation detail, not a promise. With hashed aggregates
  in 7.4, you might find this isn't true.
 
 s/might/will/


From 7.3.3, where the records were randomly inserted; note how
GROUP BY acts like I described:

test1=# select f, count(*)
test1-# from t
test1-# group by f;
f | count
---+---
1 | 3
2 | 5
4 | 4
(3 rows)

The new 7.4 attitude is *really* good to know, because, otherwise,
all our reports would break!


-- 
-
Ron Johnson, Jr. [EMAIL PROTECTED]
Jefferson, LA USA

Fair is where you take your cows to be judged.
Unknown


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] Grouping by date range

2003-08-19 Thread Ron Johnson 
On Tue, 2003-08-19 at 02:56, Alexander Litvinov wrote:
 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1
 
 I home your date field have date type. If it is try this:
 
 select date_part('year', date), count(*) from your_table group by 
 date_part('year', date) order by date_part('year', date);

Is the ORDER BY really needed here?

 for month add grouping by date_part('month', date)
 
 if you need to handle large number of rows try to add columns with year and 
 month, write triggers for filling this columns, make indexes and things 
 should be fast.
 
  date| data
  ---
  01/01/01| 123
  01/01/01| abc
  02/01/01| def
  03/03/01| hij
 
  I can see how to group by day - but how do i go about decreasing the
  precision down to months/years.

-- 
-
Ron Johnson, Jr. [EMAIL PROTECTED]
Jefferson, LA USA

My advice to you is to get married: If you find a good wife, 
you will be happy; if not, you will become a philosopher.
Socrates


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly