sum , total count

dear experts

I have a sql ilke this

select cg.NAME as CustomerGroup,COUNT(cg.NAME) as CustomerCount		 
		FROM REFDATA.CustomerGroupMap customerMap,
		REFDATA.CustomerGroup cg
		where 			
		customerMap.CustomerGroupID = cg.CustomerGroupID			
		group by cg.NAME		
		having COUNT(cg.NAME) > 1
		Order by CustomerGroup

Open in new window

           
The result i get is something like this

CustomerGroup  CustomerCount            
[PRAN/EUR]            2
[PRAN-CHF]            25
[PRAN-GBP]            25
[PRAN-HKD]            2

I want to have 2 more columns which will return me the
INDIVIDUAL TOTAL which is  = CustomerGroup count +  CustomerCount
Total count = SUM (CustomerCount)

something like

CustomerGroup  CustomerCount        INDIVIDUAL TOTAL         Total count
[PRAN/EUR]            2                              3                            52
[PRAN-CHF]            25                      27                            52       
[PRAN-GBP]            25                  26                                  52
[PRAN-HKD]            2                    3                                    52

I tried modifying the sql like this
select cg.NAME as CustomerGroup,COUNT(cg.NAME) as CustomerCount,SUM(COUNT(cg.NAME)+1) as IndividualCount,SUM(COUNT(cg.NAME)) 
		FROM REFDATA.CustomerGroupMap customerMap,
		REFDATA.CustomerGroup cg
		where 			
		customerMap.CustomerGroupID = cg.CustomerGroupID			
		group by cg.NAME		
		having COUNT(cg.NAME) > 1
		Order by CustomerGroup

Open in new window

           
            ERROR: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
            
            any sugessions ?
            
            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.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I can understand the Total Count but how do you get the Individual Count?
royjaydAuthor Commented:
individual count for each row = customer count column for each row + 1
Jim HornMicrosoft SQL Server Data DudeCommented:
It would greatly help if you could give us a data mockup of your source data and expected result set.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Scott PletcherSenior DBACommented:
select cg.NAME as CustomerGroup,
           COUNT(cg.NAME) as CustomerCount,
           COUNT(cg.NAME)+1 as IndividualCount,
           COUNT(*) OVER() as [Total count]
            FROM REFDATA.CustomerGroupMap customerMap,
            REFDATA.CustomerGroup cg
            where                   
            customerMap.CustomerGroupID = cg.CustomerGroupID                  
            group by cg.NAME            
            having COUNT(cg.NAME) > 1
            Order by CustomerGroup
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok, then use ROLLUP to give you the total. The Individual is only to add one to the actual count:
select cg.NAME as CustomerGroup,COUNT(cg.NAME) as CustomerCount,COUNT(cg.NAME) +1 as IndividualCount		 		 
FROM REFDATA.CustomerGroupMap customerMap,
INNER JOIN REFDATA.CustomerGroup cg
	ON	customerMap.CustomerGroupID = cg.CustomerGroupID			
GROUP BY ROLLUP(cg.NAME)
having COUNT(cg.NAME) > 1
Order by CustomerGroup

Open in new window

Scott PletcherSenior DBACommented:
CORRECTION:

 COUNT(*) OVER() won't do it.  It totals only result rows not all the rows from the original query.

I should have used:

SUM(COUNT(*)) OVER()


select cg.NAME as CustomerGroup,
            COUNT(cg.NAME) as CustomerCount,
            COUNT(cg.NAME)+1 as IndividualCount,
            SUM(COUNT(*)) OVER() as [Total count]
             FROM REFDATA.CustomerGroupMap customerMap,
             REFDATA.CustomerGroup cg
             where                  
             customerMap.CustomerGroupID = cg.CustomerGroupID                  
             group by cg.NAME            
             having COUNT(cg.NAME) > 1
             Order by CustomerGroup

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:
thanks all.

ScottPletcher, your sql seems to give me correct result.
what does SUM(COUNT(*)) OVER()  mean ?
how does it know to sum all the CustomerCount values ?

thanks.
Scott PletcherSenior DBACommented:
The "OVER ()" means it is a grand total for the whole query.  You can use different clauses in the OVER if you want subtotals of values.

The COUNT(*) is the standard COUNT(*) for each GROUP BY value, in the case we labeled it "CustomerCount".  The SUM(COUNT(*)) says to add all those GROUP BY counts up into one total -- which is exactly the total you're trying to get :-).
royjaydAuthor Commented:
thanks all.
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.