Link to home
Start Free TrialLog in
Avatar of Jay Roy
Jay RoyFlag for United States of America

asked on

summing up on group by rows

Dear experts
I have this sql

select customerCluster.CLUSTERID,customerCluster.ISACTIVE,customerCluster.CUSTOMERGROUPID,
COUNT(customerCluster.CLUSTERID) as groupCount
from  
CUSTOMERCluster customerCluster  
where  
customerCluster.CLUSTERID IN (500)
GROUP BY customerCluster.CLUSTERID,customerCluster.ISACTIVE,customerCluster.CUSTOMERGROUPID  
ORDER BY customerCluster.CLUSTERID

Open in new window


The resultset is get is like this
User generated image

I want to add one more column groupCountbyClusterId which will sum up the number of rows for each clusterid

Example cluster id
198 there are 2 rows
199 has 1 row
200 has 3 rows
ect.
expecting my resultset like this
User generated image

but when i add this statement to my sql >,SUM(COUNT(ccluster.CLUSTERID)) as groupCountbyClusterId
i get eror
>>Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

Any idea how to fix it?

Thanks.
ASKER CERTIFIED SOLUTION
Avatar of dsacker
dsacker
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Jay Roy

ASKER

ok thanks,
What does  COUNT(1)  represent?
thanks.
COUNT(1) simply means the count "of 1". You can put COUNT(0), COUNT(5), really any constant.

What does it buy you, though? You're not accessing a physical column to get the count, thus saving on IO and time.

Even in your original query you could substitute:
COUNT(customerCluster.CLUSTERID) as groupCount
For:
COUNT(1) as groupCount
And probably speed it up even further.