regd count(count(*)) in group by

Started by Yuva Chandoluover 23 years ago3 messages

ychandolu@ebates.com

over 23 years ago

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

Rod Taylor

rbt@zort.ca

over 23 years ago

In reply to: Yuva Chandolu (#1)

Re: regd count(count(*)) in group by

Try this:

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

Show quoted text

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

Matthew Kirkwood

matthew@hairy.beasts.org

over 23 years ago

In reply to: Rod Taylor (#2)

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.