sum and percentage in query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sum and percentage in query

Author

Topic

mohdmartin
Starting Member

22 Posts

Posted - 2007-06-26 : 08:00:43

I have following tables structure

Employee
---------
EMP_ID varchar PK
NAME varchar
DEPARTMENT_CODE varchar
POSITION_CODE int

Position
--------
POSITION_CODE int PK
POSITION_NAME varchar

Department
----------
DEPARTMENT_CODE varchar PK
DEPARTMENT_NAME varchar

Training_Module
---------------
TRA_ID varchar PK
TRA_NAME varchar
TRA_GROUP varchar (three group, A,B and C)

View_Training_Module
--------------------
MV_ID int PK
EMP_ID varchar
TRA_ID varchar
VIEW_DATE datetime

Training_Module table data like this
TRA_ID..TRA_NAME..TRA_GROUP
--------------------------------------
v01 SafetyVideo1 G1
v02 SafetyVideo2 G1
v03 SafetyVideo3 G1
V04 SafetyVideo4 G2
V05 SafetyVideo5 G2
v06 SafetyVideo6 G2
v07 SafetyVideo7 G3
v08 SafetyVideo8 G3
v09 SafetyVideo9 G3

View_Training_Module table data like this
EMP_ID.........TRA_ID....VIEW_DATE
-------------------------------------------
p0006367 V01 6/2/2007
p0006367 V02 6/2/2007
p0006367 V03 6/2/2007
p0003892 V01 6/12/2007
p0003892 V02 6/12/2007
p0003892 V03 6/12/2007
p0003890 V01 6/15/2007
p0003890 V02 6/15/2007
p0003890 V03 6/15/2007
p0001232 V04 6/16/2007
p0001232 V05 6/16/2007
p0001232 V06 6/16/2007
p0001230 V07 6/17/2007
p0001230 V08 6/18/2007
p0001230 V09 6/18/2007

We have 44 Safety training videos (15 minutes)

How can calculate the percentage of each employee in query ?

if emploee view 22 video it means that this employee
50% view the videos.

We have to calcuate
1. total number of video view by each employee, sum
2. each employee perentage of viewing.ie. percentage %
3. Group wise percentage, ?
we have three group A,B,and C, calcuate the each
group percentage in query ?

regards
Martin

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2007-06-26 : 16:18:28

Assuming no employee views the same video twice, here are the first two:

DECLARE @totalVideos int
SET @totalVideos = (SELECT COUNT(*) FROM TrainingModule)
SELECT t2.Name, COUNT(t1.emp_id) as ViewCount, COUNT(t1.emp_id)/@totalVideos as ViewPercentage FROM View_Training_Module t1 JOIN Employee t2 ON t1.emp_id = t2.emp_id GROUP BY t1.emp_id

mohdmartin
Starting Member

22 Posts

Posted - 2007-06-27 : 02:33:58

Thanks for your response.
it is working,
I use query like this.

DECLARE @totalVideos int
SET @totalVideos = (SELECT COUNT(*) FROM Training_Module)
SELECT t2.Name,t2.department_code, COUNT(t1.emp_id) as ViewCount,COUNT(t1.emp_id)*100/44 as ViewPercentage
FROM Module_View t1 JOIN Employee t2 ON t1.emp_id = t2.emp_id
GROUP BY t1.emp_id,t2.Name,t2.department_code

when I use @totalVideos in the query it show wrong percentage (high per)
hence I use like this => COUNT(t1.emp_id)*100/44

It should be work with @totalvideos because number of total video can be increase ?

How can display the department_name, Position_name in above query ?

regards
Martin

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2007-06-27 : 11:47:01

Glad you got it working, I just noticed it wouldn't have worked the way I did it because I didn't include Name in the grouping. You should be able to replace the 44 with @totalVideos now that you've added the "*100", I'm not sure why it would be too high unless people are watching the same video more than once. To display the department name and position name:

DECLARE @totalVideos int
SET @totalVideos = (SELECT COUNT(*) FROM Training_Module)
SELECT t2.Name,t3.Department_Name, t4.Position_Name, COUNT(t1.emp_id) as ViewCount,COUNT(t1.emp_id)*100/@totalVideo as ViewPercentage
FROM Module_View t1
JOIN Employee t2 ON t1.emp_id = t2.emp_id
JOIN Department t3 ON t2.Department_Code = t3.Department_Code
JOIN Position t4 ON t2.Position_Code = t4.Position_Code
GROUP BY t1.emp_id,t2.Name,t3.Department_Name, t4.Position_Name

mohdmartin
Starting Member

22 Posts

Posted - 2007-06-28 : 08:29:48

Thanks you very much. it is working.
but when I use => *100/@totalVideo as ViewPercentage it show wrong per.
/44 is OK.

We have 300 employees, and training_module have three group, 1,2 and 3. different video belong to differnt group.

How can use the same query in further group by ?
it retrived records group wise ie...

GROUP 1..viewcount..viewpercentage
-----------------------------------
Peter 10 22
Jone 16 36
Marks 8 18
...

GROUP 2.viewcount..viewpercentage
---------------------------------
Khan 15 25
Clerk 20 40
wagner 8 18
...

GROUP 3..viewcount..viewpercentage
----------------------------------
Martin 20 24
Syed 16 36
Qazi 8 18
....

or for Module_viwe group by separate query will use ?

regards
Martin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources