How to find the SUM of the Calculated Percentage Column in SQL

Question

I have a Column which calculates the Percentage , like this

Convert(decimal(5,2),(PatCount*100)/@PatientCount) as Per

Now i want the Total Sum of the above calculated Percentage. So i tried doing this,

,SUM(Convert(decimal(5,2),(PatCount*100)/@PatientCount)) as pa

But i am not geting the Desired Result . For ex

Per %    Total %
6.00     6.00
7.00     7.00
85.00    85.00

I want the total Col to print as 98%. Please can Somebody help me with this.

Answer 1

Try this

SQL FIDDLE Example

select
    cast(PatCount * 100 / @PatientCount as decimal(5,2)) as Per,
    sum(cast(PatCount * 100 / @PatientCount as decimal(5,2))) over() as Total
from Patients as P

Answer 2

For the sum to work you will need a GROUPBY, then the sum is over the group.

Simplest is to do this in a separate query. There may be a way to do this with a sub-query or you could look into a cumulative sum: Create a Cumulative Sum Column in MySQL

Answer 3

You have some options but using a CROSS APPLY should have pretty good performance characteristics.

;WITH q AS (
  SELECT SUM(CONVERT(decimal(5,2),(PatCount*100)/@PatientCount)) as Per
  FROM   YourTable
)
SELECT CONVERT(decimal(5,2),(PatCount*100)/@PatientCount) as Per
       , q.Per
FROM   YourTable
       CROSS APPLY q

or perhaps even better (to read)

;WITH q AS (
  SELECT CONVERT(decimal(5,2),(PatCount*100)/@PatientCount) as Per
  FROM   YourTable
)
SELECT q.Per, qtot.PerTot
FROM   q CROSS APPLY (SELECT SUM(Per) as PerTot FROM q) qtot