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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.