CODEWITHSUNDEEP
sqlsql-server
Faris Ahmed
Faris Ahmed

Reputation: 29

Eliminate Duplicate Hours

I'm trying to calculate the total number of training hours for every user. A course can be type=1 and type=2 my problem is the coursehours are being double counted with total of 10 hours. please see my expected result below from more info.

SELECT userid, training1.coursehours + training2.coursehours As totalhours
from user left join     
     (select sum(hours) as coursehours, userid
      from usercourses inner join
           courses
           on courses.courseid = usercourses.courseid
      where coursetype=1
      group by userid
     ) training1 left join
     (select sum(hours) as coursehours, userid
      from usercourses inner join
           courses
           on courses.courseid = usercourses.courseid
      where coursetype=2
      group by userid
     ) training2

let's say course 123 is 5 hours and is type 1 and 2 which makes it eligible to show up on both left join. The expected results should be:

UserID     TotalHours
546           5

Additional info. Data for UserCourses table:

userid   CourseID  
546      123

Data for Courses table:

courseid  coursetypeid   hours
123          1            5
123          2            5

Upvotes: 0

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Do you just want conditional aggregation?

select uc.user_id,
       sum(case when uc.coursetype = 1 then c.hours end) as hours_1,
       sum(case when uc.coursetype = 2 then c.hours end) as hours_2,
       sum(hours) as total_hours
from usercourses uc inner join
     courses c
     on c.courseid = uc.courseid
group by uc.user_id;

You only need to left join the users table if there are users who many not have any hours.

Upvotes: 1

Related Questions