ali_saigh ali_saigh - 6 months ago 24
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



I want to add
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.

Answer

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;