CODEWITHSUNDEEP
sqlsql-server
KinsDotNet
KinsDotNet

Reputation: 1560

How can I query the percentage of a value to the total of all values?

In SQL Server, I have a table called credit_hours. The table has fields College and Sum_Credit_Hours and I want to calculate the percentage of each "Sum_Credit_Hours" value as it relates to the sum of all credit hours. How can I achieve this?

Sample Data:

College      Credit_hours
------------ ------------
Liberal arts 2253.2
Social Work  442.2
Nursing      223.65
Nursing      2

Expected Result:

College      Credit_hours     Percentage
------------ ------------     ----------
Liberal Arts 2253.2            77
Social Work  442.2             16
Nursing      225.65            7

I currently have the following

SELECT College,
       sum(credit_hours),
       Percentage=( credit_hours / Sum(credit_hours)OVER() ) * 100
FROM Student_credit_hour_copy

However SQL server is stating that

Column 'Student_credit_hour_copy.COURSE_COLLEGE_NEW' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Which is strange, because it uses two aggregate functions in the select.

Upvotes: 0

Views: 59

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93734

Use Sum() Over() window function to find the sum of all credithours and divide the each credithours with the sum to find the percentage

SELECT college,
       credit_hours,
       Percentage=( credit_hours / Sum(Credit_hours)OVER() ) * 100
FROM  (SELECT college,
              Sum(credit_hours) credit_hours
       FROM   Yourtable
       GROUP  BY college) a

SQLFIDDLE DEMO

Upvotes: 1

yoelbenyossef
yoelbenyossef

Reputation: 463

For the exact code, we'll need to see what your data structure looks like. But if you have a query that gives you the sum, it'll be the same query with avg in it's place. You may have to round it though.

So, something like,

Select sum(column1) Total_Column1, avg(column1) Average_Column1
from table_name
Group by PK_Column

Upvotes: 0

Related Questions