-1

My query is adding extra rows to the output and I don't understand why. I know that is must me something simple, but I am very new to programing and can not figure out what is creating the extra row.

SELECT u.firstname AS 'First Name', u.lastname AS 'Last Name', sum(bh.service_hrs) AS 'Total Billable Hrs', sum(ph.service_hrs) AS 'Total PTO Hrs', sum(hh.service_hrs) AS 'Total Holiday Hrs' 
FROM users AS u 
JOIN billable_hrs AS bh ON (bh.user_id = u.user_id) 
JOIN pto_hrs AS ph ON (ph.user_id = u.user_id) 
JOIN holiday_hrs AS hh ON (hh.user_id = u.user_id) 
GROUP BY u.user_id, bh.service_hrs, ph.service_hrs, hh.service_hrs

billable_hrs

billable_hrs table

pto_hrs

pto_hrs table

holiday_hrs

holiday_hrs table

Query Results

Query Results

Barmar
  • 741,623
  • 53
  • 500
  • 612
Philip
  • 3
  • 2
  • You likely have multiple one-to-many relationships not directly related to one another; that gives you Cartesian products of the subsets. _As an example of a Cartesian product, if you join (1,2,3) to (4,5), you get six results._ In your scenario, you typically need a subquery for each `sum`. Also, you'll probably be wanting `LEFT JOIN` to those subqueries, since it's feasible a user may lack records in one or more of those hours tables. – Uueerdo Aug 23 '19 at 22:38
  • Probably learn how `JOIN` works. – Eric Aug 23 '19 at 22:59

1 Answers1

0

Since you use GROUP BY u.user_id, bh.service_hrs, ph.service_hrs, hh.service_hrs, you get a separate row for each combination of those values. Which defeats the point of summing those columns, since you won't combine multiple rows.

What you need to do is join with subqueries that calculate the sum from each table. And use LEFT JOIN in case a uer doesn't have any hours of one of the types.

SELECT u.firstname AS 'First Name', u.lastname AS 'Last Name', IFNULL(billable_hours, 0) AS 'Total Billable Hrs', IFNULL(pto_hours, 0) AS 'Total PTO Hrs', IFNULL(holiday_hours, 0) AS 'Total Holiday Hrs' 
FROM users AS u 
LEFT JOIN (
    SELECT user_id, SUM(service_hours) AS billable_hours
    FROM billable_hrs
    GROUP BY user_id) AS bh ON (bh.user_id = u.user_id) 
LEFT JOIN (
    SELECT user_id, SUM(service_hours) AS pto_hours
    FROM pto_hrs
    GROUP BY user_id) AS ph ON (ph.user_id = u.user_id) 
LEFT JOIN (
    SELECT user_id, SUM(service_hours) AS holiday_hours
    FROM holiday_hrs
    GROUP BY user_id) AS hh ON (hh.user_id = u.user_id)

You can't do the summing in the main query when you're summing from multiple tables. See Join tables with SUM issue in MYSQL for an explanation.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Thank you so much. I had tried subqueries before, but not in the JOIN statements. like you did. This solved my problem. – Philip Aug 24 '19 at 11:55