Hi,

We have a query "select count(count(*)) from test group by
trunc(test_date)". This works fine with Oracle but when moving to postgres I
changed it to "select count(count(*)) from test group by date_trunc('day',
test_date)" but I get the following error

ERROR:  Aggregate function calls may not be nested

Can some one help me...

Thanks
Yuva

Fri, 26 Jul 2002 13:03:40 -0700
Yuva Chandolu <ychandolu@ebates.com>
regd count(count(*)) in group by
Try this:

SELECT count(*)
  FROM (
	SELECT count(*)
	FROM test
	GROUP BY date_trunc('day', test_date)
	) as qry;


On Fri, 2002-07-26 at 16:03, Yuva Chandolu wrote:
> Hi,
> 
> We have a query "select count(count(*)) from test group by
> trunc(test_date)". This works fine with Oracle but when moving to postgres I
> changed it to "select count(count(*)) from test group by date_trunc('day',
> test_date)" but I get the following error
> 
> ERROR:  Aggregate function calls may not be nested
> 
> Can some one help me...
> 
> Thanks
> Yuva
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/users-lounge/docs/faq.html
> 



26 Jul 2002 16:50:31 -0400
Rod Taylor <rbt@zort.ca>
Re: regd count(count(*)) in group by
On 26 Jul 2002, Rod Taylor wrote:

> Try this:
>
> SELECT count(*)
>   FROM (
> 	SELECT count(*)
> 	FROM test
> 	GROUP BY date_trunc('day', test_date)
> 	) as qry;

Or this:

	SELECT COUNT(*)
	FROM (
		SELECT DISTINCT(date_trunc('day', test_date))
		FROM test
	);

Matthew.


Fri, 26 Jul 2002 23:09:08 +0100 (BST)
Matthew Kirkwood <matthew@hairy.beasts.org>
Re: regd count(count(*)) in group by