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
result1

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
result2.png

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.
royjaydAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
dsackerContract ERP Admin/ConsultantCommented:
Try this:
SELECT  customerCluster.CLUSTERID,
        customerCluster.ISACTIVE,
        customerCluster.CUSTOMERGROUPID,
        COUNT(customerCluster.CLUSTERID) as groupCount,
       (SELECT COUNT(1) FROM CUSTOMERCluster customerCluster c2
        WHERE  c2.ClusterID = customerCluster.ClusterID) AS groupCountByClusterId
FROM    CUSTOMERCluster customerCluster  
WHERE   customerCluster.CLUSTERID IN (500)
GROUP BY customerCluster.CLUSTERID,
         customerCluster.ISACTIVE,
         customerCluster.CUSTOMERGROUPID  
ORDER BY customerCluster.CLUSTERID

Open in new window

A JOIN would also work, but since there is only one additional field you need, it's not any bigger effort to simply add a subselect in your SELECT list.

But if you want to know the JOIN option, it would code like this:
SELECT  customerCluster.CLUSTERID,
        customerCluster.ISACTIVE,
        customerCluster.CUSTOMERGROUPID,
        COUNT(customerCluster.CLUSTERID) as groupCount,
        c2.groupCountByClusterId
FROM    CUSTOMERCluster customerCluster  
INNER JOIN (SELECT ClusterID, COUNT(1) AS groupCountByClusterId
            FROM   CUSTOMERCluster
            GROUP BY ClusterID) c2
        ON c2.ClusterID = customerCluster.ClusterID
WHERE   customerCluster.CLUSTERID IN (500)
GROUP BY customerCluster.CLUSTERID,
         customerCluster.ISACTIVE,
         customerCluster.CUSTOMERGROUPID  
ORDER BY customerCluster.CLUSTERID

Open in new window

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
royjaydAuthor Commented:
ok thanks,
What does  COUNT(1)  represent?
thanks.
0
 
dsackerContract ERP Admin/ConsultantCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.