How to Perform an Aggregate Function?

[Former Member]

Hi,

I'm trying to define in Designer 6.5 an object with the following select: (example)

sum (count (distinct REPORTING.table.object_name))

the SQL parsing restitues me this error --> "The SQL can not perform an aggregate function on an expression containing an aggregate or a subquery".

Can anyboby explain me the problem and above all how to solve it?

Thanks

Riccardo

[Former Member · Accepted solution]

Hi,

You cannot mix different aggregations functions in the same SQL expression: this is a SQL limitation for all databases.

You need to use subselect to achieve your requirement.

See the following example that works on SQL Server:

Select sum(a._count) from 
(Select count (distinct cust_id) as _count from Club.dbo.Customer) a

Regards,

Didier