Sum columns on different tables and multiply by value of a column on another table

Question

I need to compute employees' monthly salaries based on meetings attended, deductions and bonuses given; Employees have different pay per meeting based on their job position.

The solution is: salary = (Pay_per_minute * meetings_attended) + bonuses - deductions ;

I have four tables:

Jobs: Id, title, pay_per_meeting

Employees: Id, Name, job_id

Bonuses: Id, amount, employee_id, date

Deductions: Id, amount, employee_id, date

Meetings: Id, employee_id, date

SELECT
COUNT(meetings.employee_id) as meetings_attended, 
COUNT(deductions.amount) as debt, 
COUNT(bonuses.amount) bonus, 
(SELECT jobs.pay_per_attendance from jobs where jobs.id = (select job_id from employees where id=meetings.employee_id)) as pay,
((meetings_attended * pay) + bonus - debt) as salary 
FROM meetings 
JOIN deductions ON deductions.employee_id = meetings.employee_id 
JOIN bonuses ON bonuses.employee_id = meetings.employee_id 
WHERE meetings.employee_id = 1 
GROUP BY MONTH(meetings.date), MONTH(deductions.date), MONTH(bonuses.date)

The above query returns many incorrect values whenever i remove the salary line but gives error of unknown column pay, meetings_attended, debt and bonus, am sure something is wrong with the grouping but i can't just see it.

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Apr 14 '18 at 06:55

score 1 · Answer 1 · answered Apr 13 '18 at 23:31

You can't refer to column aliases in the same select list as they're defined, you need to refer to the underlying column. And a subquery can't access an aggregate calculated in the main query. You need to repeat the aggregate expression, or move everything into a subquery and do the calculation with it in an outer query.

Also, all your COUNT() expressions are going to return the same thing, since they're just counting rows (I assume none of the values can be NULL). You probably want COUNT(DISTINCT <column>) to get different counts, and you need to use a column that's unique, so they should be the primary key column, e.g. COUNT(DISTINCT deductions.id).

Another problem is that when you try to sum and count values when you have multiple joins, you end up with a result that's too high, because rows get duplicated in the cross product of all the tables. See Join tables with SUM issue in MYSQL. The solution is to calculate the sums from each table in subqueries.

SELECT m.month, m.meetings_attended, d.debt, b.bonus, 
    m.meetings_attended * j.pay_per_meeting + b.amount - d.amount AS salary
FROM (
    SELECT MONTH(date) AS month, COUNT(*) AS meetings_attended
    FROM meetings
    WHERE employee_id = 1
    GROUP BY month) AS m
JOIN (
    SELECT MONTH(date) AS month, COUNT(*) AS bonus, SUM(amount) AS amount
    FROM bonuses
    WHERE employee_id = 1
    GROUP BY month) AS b ON m.month = b.month
JOIN (
    SELECT MONTH(date) AS month, COUNT(*) AS debt, SUM(amount) AS amount
    FROM deductions
    WHERE employee_id = 1
    GROUP BY month) AS d ON m.month = d.month
CROSS JOIN employees AS e
JOIN jobs AS j ON j.id = e.job_id
WHERE e.employee_id = 1

Thanks! However am getting this error **"Unknown column 'j.pay_per_meeting' in 'field list'"** — Peter Moses, Apr 14 '18 at 00:03
You wrote: `Jobs: Id, title, pay_per_meeting` so isn't that the name of the column? Or is it `pay_per_attendance` like you wrote in your query? — Barmar, Apr 14 '18 at 01:22

Sum columns on different tables and multiply by value of a column on another table

1 Answers1