CODEWITHSUNDEEP
phpmysql
Peter Moses
Peter Moses

Reputation: 2137

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

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.

Upvotes: 0

Views: 344

Answers (1)

Barmar
Barmar

Reputation: 781769

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

Upvotes: 1

Related Questions