CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
Naresh Cherukuri
Naresh Cherukuri

Reputation: 33

How Can I Get percentile(%) Columns Using TSQL Code

I have 2 Columns in a table... which are :

HCode           No_of contacts
AA1             78
AA2             10 
AA3             09
AA4             14
AA5             10 
AA1             21

I have written the following code: 

select HCode, Sum(No_of contacts) as NoOFContacts
from #T
Group by  HCode 
order by  HCode

Using the above code, how can I populate a Percentage column based on max([No_of contacts])?

Upvotes: 1

Views: 93

Answers (4)

Brahmanand Choudhary
Brahmanand Choudhary

Reputation: 21

You can try with the following query:

select HCode,Sum(No_of contacts ) as NoOFContacts,(Sum(No_of contacts)*100/(Select Count(*) from #T)) as Percentage
from #T
group by HCode 
order by HCode

Upvotes: 0

Stijn Wynants
Stijn Wynants

Reputation: 107

You can do that with a nested sql statement

select Hcode, (NumberAcc/SumAcc)*100 as Percentage, Sumacc
from
(select Hcode, Sum(no_Of contacts) as SumAcc, no_OfContacts as NumberAcc
from #t
group by HCode, no_OfContacts) as T

Upvotes: 0

jpw
jpw

Reputation: 44871

Maybe this is what you want?

select 
    HCode, 
    Sum([No_of contacts]) as NoOFContacts, 
    Sum([No_of contacts])*100/(select Sum([No_of contacts]) from #t) as Percentage
from #t
Group by HCode 
order by HCode

This would render the following result:

HCode      NoOFContacts Percentage
---------- ------------ -----------
AA1        99           69
AA2        10           7
AA3        9            6
AA4        14           9
AA5        10           7

(5 row(s) affected)

Upvotes: 1

GarethD
GarethD

Reputation: 69749

You can use Analytical functions to get the max number of contracts, without having to do a separate subuery to get this:

CREATE TABLE #T (HCode VARCHAR(3), No_of_contacts INT);

INSERT #T (HCode, No_of_contacts)
VALUES ('AA1', 78), ('AA2', 10), ('AA3', 09), 
        ('AA4', 14), ('AA5', 10), ('AA1', 21);

SELECT  HCode,
        No_of_contacts,
        PercentOfContracts = 100.0 * No_of_contacts / MAX(No_of_contacts) OVER()
FROM    (   SELECT  HCode,
                    No_of_contacts = SUM(No_of_contacts)
            FROM    #T
            GROUP BY Hcode
        ) AS t;

Upvotes: 0

Related Questions