Calculate salary of tutor based on distinct sittings using mysql

Question

I have the following table denoting a tutor teaching pupils in small groups. Each pupil has an entry into the database. A pupil may be alone or in a group. I wish to calculate the tutors "salary" as such: payment is based on time spent - this means that for each sitting (with one or more pupils) only one sitting will be calculated - distinct sittings! The start and end times are unix times.

<pre>
    start   end     attendance
1359882000  1359882090  1
1359867600  1359867690  0
1359867600  1359867690  1
1359867600  1359867690  0
1360472400  1360477800  1
1360472400  1360477800  1
1359867600  1359867690  1
1359914400  1359919800  1
1360000800  1360006200  1
1360000800  1360006200  0
1360000800  1360006200  1

</pre>

This is what I tried: with no success - I can't get the right duration (number of hours for all distinct sittings)

    SELECT YEAR(FROM_UNIXTIME(start)) AS year, 
    MONTHNAME(STR_TO_DATE(MONTH(FROM_UNIXTIME(start)), '%m')) AS month,
    COUNT(DISTINCT start) AS sittings,
    SUM(TRUNCATE((end-start)/3600, 1)) as duration 
    FROM schedules
    GROUP BY
    YEAR(FROM_UNIXTIME(start)), 
    MONTH(FROM_UNIXTIME(start))
    

Thanks for your proposals / support!

EDIT: Required results

Rate = 25
Year    Month   Sittings    Duration    Bounty
2013    February     2         2.2          2.2*25
2013    April        4         12.0         12.0*25

Answer 1

You could probably do something with subqueries, I've had a play with SQL fiddle, how does this look for you. Link to sql fiddle : http://sqlfiddle.com/#!2/50718c/3

SELECT
  YEAR(d.date) AS year,
  MONTH(d.date) AS month,
  COUNT(*) AS sittings,
  SUM(d.duration) AS duration_mins
FROM (
  SELECT
    DATE(FROM_UNIXTIME(s.start)) AS date,
    s.attendance,
    end-start AS duration
  FROM schedules s
) d

GROUP BY 
  year,
  month

I couldn't really see where attendance comes into this at present, you didn't specify. The inner query is responsible for taking the schedules, extracting a start date, and a duration (in seconds).

The outer query then uses these derived values but groups them up to get the sums. You could elaborate from here i.e. maybe you only want to select where attendance > 0, or maybe you want to multiply by attendance.

In this next example I have done this, calculating the duration in hours instead, and calculating the applicable duration for where sessions have >1 attendance along with the appropriate bounty assuming bounty == hours * rate : http://sqlfiddle.com/#!2/50718c/21

SELECT
  YEAR(d.date) AS year,
  MONTH(d.date) AS month,
  COUNT(*) AS sittings,
  SUM(d.duration) AS duration,
  SUM(
    IF(d.attendance>0,1,0)
  ) AS sittingsWorthBounty,
  SUM(
    IF(d.attendance>0,d.duration,0)
  ) AS durationForBounty,
  SUM(
    IF(d.attendance>0,d.bounty,0)
  ) AS bounty

FROM (
  SELECT
    DATE(FROM_UNIXTIME(s.start)) AS date,
    s.attendance,
    (end-start)/3600 AS duration,
    (end-start)/3600 * @rate AS bounty
  FROM schedules s,
  (SELECT @rate := 25) v
) d

GROUP BY 
  year,
  month

The key point here, is that in the subquery you do all the calculation per-row. The main query then is responsible for grouping up the results and getting your totals. The IF statements in the outer query could easily be moved into the subquery instead, for example. I just included them like this so you could see where the values came from.