ali_saigh - 2 years ago 108
SQL Question

# How to calculate percentage in SQL Server 2008

I have a query of 4 columns

• directorate,

• t1 for number of the employees under the directorate who took the courses

• t2 for number of the courses that the directorate took

• t3 for the total prize that spend of each directorate

`t4`
for the percentage of the total prize of every directorate

The formula is
`(t3 / total sum of t3) * 100`

This is the query:

``````with cte1 as
(
select
vwemployee.directorateName,
count(distinct vwemployeeCourse.employeeId) as t1
from
vwemployee, vwemployeeCourse
where
vwemployee.directorateName = vwemployeeCourse.directorateName
group by
vwemployee.directorateName
), cte2 as
(
select
vwemployee.directorateName,
count(vwemployee.directorateName) as t2
from
vwemployee, employeeCourse
where
vwemployee.Id = employeeCourse.employeeId
group by
vwemployee.directorateName
), cte3 as
(
select
vwemployeeCourse.directorateName,
sum(vwCourse.cost) as t3
from
vwemployeeCourse, vwCourse
where
vwemployeeCourse.courseId = vwCourse.Id
group by
vwemployeeCourse.directorateName
)
select
cte1.directorateName, cte1.t1, cte2.t2, cte3.t3
from
cte1
inner join
cte2 on cte1.directorateName = cte2.directorateName
inner join
cte3 on cte2.directorateName = cte3.directorateName
``````

So far I have learned how to combine queries together, but I want to learn how to use operation like above.

You can use window fucntions:

``````with . . .
select cte1.directorateName, cte1.t1, cte2.t2, cte3.t3,
cte3.t3 / sum(cte3.t3) over ()
from cte1 inner join
cte2
on cte1.directorateName = cte2.directorateName inner join
cte3
on cte2.directorateName = cte3.directorateName;
``````

If `t3` is an integer, then you need to convert it to a number with a decimal point (SQL Server does integer division):

``````with . . .
select cte1.directorateName, cte1.t1, cte2.t2, cte3.t3,
cte3.t3*1.0 / sum(cte3.t3) over ()
from cte1 inner join
cte2
on cte1.directorateName = cte2.directorateName inner join
cte3
on cte2.directorateName = cte3.directorateName;
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download