Jay Roy
asked on
summing up on group by rows
Dear experts
I have this sql
The resultset is get is like this
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
but when i add this statement to my sql >,SUM(COUNT(ccluster.CLUST ERID)) 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.
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
The resultset is get is like this
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
but when i add this statement to my sql >,SUM(COUNT(ccluster.CLUST
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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:
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.CLUS TERID) as groupCount
For:
COUNT(1) as groupCount
And probably speed it up even further.
ASKER
What does COUNT(1) represent?
thanks.