CODEWITHSUNDEEP
mysqlsqlaggregate-functions
franglais
franglais

Reputation: 938

MySQL Sum not correct and join

I'm building a holiday system and one of the features is being able to buy extra holiday which you can do at several points of the year, so I'm wanting to see the total number of days holiday, how much has been booked and how much has been bought by each user.

I'm doing a query 

SELECT  hr_user.name AS username, 
        hr_user.user_id,
        SUM(working_days) AS daysbooked, 
        sum(hr_buyback.days) AS daysbought 
FROM hr_leave 
inner join hr_user on hr_user.user_id = hr_leave.user_id
left outer join hr_buyback on hr_buyback.user_id = hr_user.user_id
where    active = 'y' 
     and hr_leave.start_date between '2012-01-01' and '2012-12-31' 
     and (hr_leave.status = 'approved' OR hr_leave.status = 'pending')
GROUP BY hr_user.name, hr_user.user_id

Now this is bringing back results in the daysbought column waaaay higher than what I was expecting, which is odd because when I get rid of the sum and just have hr_buyback.days it shows all the individual values I'd expect (except I'd much rather they were summed)

Secondly, in MySQL can you do what you can in MSSQL which is 

left outer join hr_buyback on (select hr_buyback.user_id where buy_sell = 'buy') = hr_leave.user_id

?

Relevant table definitions (I assume this is what you mean?):

hr_buyback

buyback_id  int(11) NO  PRI     auto_increment
user_id     int(11) NO          
days        int(11) NO          
buy_sell    varchar(10) NO          
status      varchar(10) NO      pending 
year        int(11) NO

hr_user

user_id     int(11) NO  PRI     auto_increment
name        varchar(40) NO          
email       varchar(40) NO  UNI     
level       int(5)  YES         
manager_id  int(11) NO          
team_id     int(11) YES         
active      varchar(2)  NO      y   
holidays_day    int(11) NO          
start_date  timestamp   NO      CURRENT_TIMESTAMP   
password    varchar(60) NO          
division_id int(11) YES         
day_change  int(5)  NO      0   
priv_hours  varchar(2)  NO      n   
po_level    int(2)  YES     0   
po_signoff  int(10) YES

hr_leave

leave_id    int(11) NO  PRI     auto_increment
user_id     int(11) NO          
start_date  date    NO          
end_date    date    NO          
day_type    varchar(10) NO          
status      varchar(20) NO      pending 
working_days    varchar(5)  NO          
leave_type  int(11) NO          
cancel      int(11) NO      0   
date        timestamp   NO      CURRENT_TIMESTAMP

Upvotes: 1

Views: 221

Answers (1)

Nis Jørgensen
Nis Jørgensen

Reputation: 36

The problem is probably that you will get one copy of each row from hr_buyback for each matching row in hr_leave.

I assume that it is possible to have more than one hr_buyback row per user, and that it is possible to have a hr_buyback row without a hr_leave row. If so, you'll probably want something like this:

SELECT  hr_user.name AS username, 
    hr_user.user_id,
    SUM(working_days) AS daysbooked, 
        (SELECT SUM(days) 
        FROM hr_buyback 
        WHERE hr_buyback.user_id = hr_user.user_id) AS daysbought 
FROM  hr_user  
left join hr_leave on hr_user.user_id = hr_leave.user_id
where    active = 'y' 
 and hr_leave.start_date between '2012-01-01' and '2012-12-31' 
 and (hr_leave.status = 'approved' OR hr_leave.status = 'pending')
GROUP BY hr_user.name, hr_user.user_id

Upvotes: 2

Related Questions