CODEWITHSUNDEEP
sqlt-sql
Frenchi In LA
Frenchi In LA

Reputation: 3169

Get Avg for this query

Let’s say I have a table with the following columns: EmployeeID, PayEntity, HourWorked, Date where PayEntity can be 1:Normal, 2:OverTime, etc How can I get the average of Hours worked for a period of time for example if we do have on my table those data:

1, 1, 40, 7/25/12
1, 2, 5, 7/25/12
2, 1, 30, 7/25/12

The average for this date must be (40+5)+30/2 -- 37.5

Upvotes: 1

Views: 46

Answers (2)

Ross McLellan
Ross McLellan

Reputation: 1881

Along the same lines, but didn't know there was an AVG function

SELECT employee_id, total_hours, days, total_hours/days as average, 
FROM (
    SELECT employee_id, SUM(hours) as total_hours, COUNT(date) as days FROM `employee_hours_table` GROUP BY `employee_id`
) data

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 838066

Try using a subselect where you find the total hours for each employee per day, and an outer select where you calculate the average per day:

SELECT Date, AVG(HourWorked) AS HourWorked
FROM 
(
    SELECT Date, SUM(HourWorked) AS HourWorked
    FROM yourtable
    GROUP BY EmployeeId, Date
) T1
GROUP BY Date

See it working online: sqlfiddle

Upvotes: 3

Related Questions