I have two tables related by a foreign key. employee table is as follows:
+----+------------+-----------+---------------+
| id | first_name | last_name | billable_rate |
+----+------------+-----------+---------------+
| 1 | James | Maxston | 300 |
| 2 | Sean | Scott | 500 |
+----+------------+-----------+---------------+
timesheet table is as follows:
+----+----------+------------+------------+----------+-------------+
| id | project | date | start_time | end_time | employee_id |
+----+----------+------------+------------+----------+-------------+
| 1 | AIT | 2020-07-20 | 09:00:00 | 12:00:00 | 1 |
| 2 | Axiiscom | 2020-06-20 | 15:00:00 | 17:00:00 | 1 |
| 3 | AIT | 2020-07-20 | 13:00:00 | 18:00:00 | 1 |
| 4 | AIT | 2020-07-01 | 11:00:00 | 14:00:00 | 2 |
| 5 | AIT | 2020-06-21 | 11:00:00 | 12:00:00 | 2 |
+----+----------+------------+------------+----------+-------------+
Running the query below:
SELECT
project, employee_id, @hours_worked := SUM(timestampdiff(HOUR, start_time, end_time)) AS number_of_hours,
@hourly_rate :=my_db.employee.billable_rate AS unit_price,
@hours_worked * @hourly_rate AS cost
FROM
my_db.timesheet
INNER JOIN
my_db.employee ON my_db.employee.id = my_db.timesheet.employee_id
WHERE
project = "AIT"
GROUP BY
employee_id;
yields the following result:
+---------+-------------+-----------------+------------+-------------------------------------+
| project | employee_id | number_of_hours | unit_price | cost |
+---------+-------------+-----------------+------------+-------------------------------------+
| AIT | 1 | 8 | 300 | 1200.000000000000000000000000000000 |
| AIT | 2 | 4 | 500 | 2000.000000000000000000000000000000 |
+---------+-------------+-----------------+------------+-------------------------------------+
when instead I expected it would yield this result:
+---------+-------------+-----------------+------------+-------------------------------------+
| project | employee_id | number_of_hours | unit_price | cost |
+---------+-------------+-----------------+------------+-------------------------------------+
| AIT | 1 | 8 | 300 | 2400.000000000000000000000000000000 |
| AIT | 2 | 4 | 500 | 2000.000000000000000000000000000000 |
+---------+-------------+-----------------+------------+-------------------------------------+
Where am I going wrong in my query?
Instead of using variables a simple aggregation can do the processing you need. For example, you can do:
select
t.project,
t.employee_id,
sum(timestampdiff(HOUR, t.start_time, t.end_time)) as number_of_hours,
max(e.billable_rate) as unit_price,
sum(timestampdiff(HOUR, t.start_time, t.end_time))
* max(e.billable_rate) as cost
from my_db.timesheet t
join my_db.employee e on e.id = t.employee_id
where t.project = 'AIT'
group by t.project, t.employee_id
In MySQL you don't have much control over when @variable values are updated while processing queries. So avoid them for the purpose you're using. They lead to confusion.
You can still make your business logic (in your case the computation of cost) reasonably easy to read.
Try using a nested query instead: something like this.
SELECT project, employee_id, number_of_hours, unit_price,
unit_price * number_of_hours AS cost
FROM (
SELECT my_db.timesheet.project,
my_db.timesheet.employee_id,
SUM(timestampdiff(HOUR, start_time, end_time)) AS number_of_hours,
my_db.employee.billable_rate AS unit_price
FROM my_db.timesheet
INNER JOIN my_db.employee
ON my_db.employee.id = my_db.timesheet.employee_id
GROUP BY my_db.timesheet.project,
my_db.timesheeet.employee_id,
my_db.employee.billable_rate
) summary
WHERE project = 'AIT'
ORDER BY employee_id
MySQL's query planner does a reasonably good job of handing this kind of nested query, so you don't have to worry too much about performance.
If you want you can define the inner query as a view. Then your outer query is really easy to read.
SELECT project, employee_id, number_of_hours, unit_price,
unit_price * number_of_hours AS cost
FROM my_db.project_summary
WHERE project = 'AIT'
ORDER BY employee_id
To create the view, you do
CREATE VIEW my_db.project_summary AS
SELECT my_db.timesheet.project,
my_db.timesheet.employee_id,
SUM(timestampdiff(HOUR, start_time, end_time)) AS number_of_hours,
my_db.employee.billable_rate AS unit_price
FROM my_db.timesheet
INNER JOIN my_db.employee
ON my_db.employee.id = my_db.timesheet.employee_id
GROUP BY my_db.timesheet.project,
my_db.timesheeet.employee_id,
my_db.employee.billable_rate
Easy to read is good.