2

may I ask for assistance regarding this code, getting different SUM() from different tables doesn't seem to work in this code. Apologies for this matter as I completely forgot how to do this anymore.

It only works when I only use sum for attendance query.

$sql = "SELECT *, 

        SUM(num_hr) AS total_hr, 
        attendance.employee_id AS empid, 
        employees.employee_id AS employee, 
        position.description AS job_description, 
        SUM(deductions.amount) AS total_amount_deduction

        FROM attendance

        LEFT JOIN employees ON employees.id=attendance.employee_id 
        LEFT JOIN position ON position.id=employees.position_id 
        LEFT JOIN deductions ON deductions.deduction_id=employees.deduction_id 

        WHERE date BETWEEN '$from' AND '$to'

        GROUP BY attendance.employee_id

        ORDER BY employees.lastname ASC, employees.firstname ASC";

I was hoping to get the results, but its showing different SUM values on every result.

When I remove SUM(deductions.amount) AS total_amount_deduction and LEFT JOIN deductions ON deductions.deduction_id=employees.deduction_id it works fine.

Dharman
  • 30,962
  • 25
  • 85
  • 135
pjustindaryll
  • 377
  • 3
  • 14
  • 1
    Does the query work if you don't select the `*`? – Yang Jul 02 '19 at 04:27
  • 1
    Your grouping clause is wrong , have to include the other columns employees.employee_id, position.description – Fact Jul 02 '19 at 04:29
  • 1
    When you use group by, each column in the select clause must be either defined via an Aggregate function (like MAX, MIN, AVG, ...) or must be included in the group by clause... amend the GROUP BY as `GROUP BY attendance.employee_id, employees.employee_id, position.description` and mentioned the columns in * explicitly in group by – MJoy Jul 02 '19 at 04:30
  • 1
    Not necessarily. MySQL allows selecting non-group-by columns: https://stackoverflow.com/questions/1023347/mysql-selecting-a-column-not-in-group-by. – Yang Jul 02 '19 at 04:31
  • @MJoy i did that, but still it doesn't work. I'm still getting wrong values from SUM(). – pjustindaryll Jul 02 '19 at 05:02
  • @Yang it doesn't seem to work when removing *. – pjustindaryll Jul 02 '19 at 05:05
  • @pjustindaryll are you sure it doesn't work removing star – kiran gadhe Jul 02 '19 at 05:53
  • @kirangadhe yes. is my structure wrong? I feel like this will cause a problem someday even if this will work. If I will get similar deduction_id from different employees, it will be a problem. – pjustindaryll Jul 02 '19 at 08:09

0 Answers0