Nested Aggregates?

From: John <john(at)akadine(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Nested Aggregates?
Date: 2000-07-20 16:02:30
Message-ID: Pine.BSF.4.21.0007201150180.16251-100000@db.akadine.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

I know nested aggregates aren't allowed, or at least not implicitly.
Is there a way to get around this.?
Or does someone feel like droppng some other hints this way?

Problem : (not really)
I have a table (id, date, ordertype, etc...)
Based on the type i want to be able to get the counts of how many people
have ordered from a certain type of ordertype.

so i.
select id, count(*) as cnt from T1 where ordertype = 'Q' group by id;

This will give me the number of times each id has ordered type Q.
I need a way to find out how many times, or how many ids have ordered type
Q once, twice, thrice, etc.

I can accomplish this by doing a select into temp with the above
statement. And then doing.
Select cnt, count(*) from TEMP group by cnt;

This really doesn't seem the most efficient way to do this by me. And i
know nested aggs aren't allowed (or don't like me). And i'm trying to
fidn a way to eliminate the insert / select step.

so the main question is.
how do i accomplish ths better? can anyone help?
and how can i implement it as a subselect that will work?
will that be more efficient.

Sorry if the question's seem simple / trivial. But it's been gnawing at
me for a while that this doesn't seem to be the most efficient way to
handle this select. And i can't figure out another way that postgres will
accept.

Thanks in advance.
.jtp

Browse pgsql-sql by date

  From Date Subject
Next Message Markus Wagner 2000-07-21 07:41:27 referencing serials
Previous Message omid omoomi 2000-07-20 14:03:27 Re: primary key question