Here is a 'fairly' simple solution to your problem.

First a table is 'created' which contains the totals for each column.

Then this table is used to calculate the percentages for all the columns.

Two solutions which are both about equal. First solution is using a 'WITH' statement which creates a 'temporary' table/view for the totals. Then the totals are used in the end part of the statement.

Second example does the same but actually build a #counted table for the totals.

--

-- T contains the totals for each column

--

;

With

T as (

select SUM(abington) S_A, SUM(belmontst) S_B, SUM(bridgewater) S_C from #test)

select *

,100.0*Abington/S_A Percent_A

,100.0*BelmontSt/S_B Percent_B

,100.0*Bridgewater/S_C Percent_C

from #Test cross join T

--

-- Alternative:

--

-- #Counted contains the totals for each column

--

select SUM(abington) S_A, SUM(belmontst) S_B, SUM(bridgewater) S_C into #Counted from #test

select *

,100.0*Abington/S_A Percent_A

,100.0*BelmontSt/S_B Percent_B

,100.0*Bridgewater/S_C Percent_C

from #Test cross join counted

drop table #Test

drop table #counted

There are other solutions which can calculate the percentage 'more' directly, but code for that is more advanced.

Weaknesses of the example and the solution is that the number of columns depends on the number of sites with patients. Code has to change if another site is added, this is a weakness.

For the percentages their is the weakness that a site with no patients yet will result in an error because of the division by zero.

Questions to others in the group:

With examples as these how do we prevent a division by zero. (Because the complete column does only contain zero's). (The same for averaging fields if there are only zero fields).

Please tel if this solution works for you, or helps you along.

Ben