mysql - SQL nested select sum returns wrong integer -


i have following select statement:

   select      t.name,     c.name competence_name,     thc.competence_level_id requiredlevel,     (select sum(uhc.competence_level_id) user_has_competence uhc inner join user u on u.id = uhc.user_id competence_id = c.id , u.title_id = t.id group t.id) current_level,     (select              round(sum(uhc.competence_level_id)/ count(uhc.user_id))                      user_has_competence uhc         join user u on u.id = uhc.user_id                     competence_id = c.id                 , u.title_id = t.id group t.id) - thc.competence_level_id gap, c.id     title_has_competence thc         join     title t on t.id = thc.title_id         join     competence c on c.id = thc.competence_id 

this returns following result:

    'head of it', 'office', '7', '16', '1', '524' 'head of it', 'empatisk', '2', '5', '1', '527' 'head of it', 'målrettet', '5', '12', '1', '529' 'head of it', 'udadvendt', '10', null, null, '525' 'webudvikler', 'office', '2', '3', '1', '524' 'webudvikler', 'outlook', '3', '4', '1', '526' 'webudvikler', 'målrettet', '6', '10', '4', '529' 'webudvikler', 'back-end', '9', '9', '0', '534' 'webudvikler', 'infosoft', '10', '5', '-5', '532' 

however result invalid:

in first row see current level sum = 16 should 9 (6 + 3)

the gap incorrect gap should 9 / 2 = 4.5 - 7 = -2.5

so question doing wrong? why sum() function returning way much.

also note count() returns right value = 2

the tables

title_has_competence

 id    title_id competence_id competence_level_id '82', '165', '527', '2' '85', '166', '524', '2' '86', '166', '526', '3' '83', '165', '529', '5' '87', '166', '529', '6' '81', '165', '524', '7' '88', '166', '534', '9' '84', '165', '525', '10' '89', '166', '532', '10' 

user_has_competence

 user_id, competence_id, competence_level_id, progression '1', '524', '6', '0' '1', '527', '4', '0' '1', '529', '3', '0' '2', '524', '10', '0' '2', '527', '1', '0' '2', '529', '9', '0' '3', '524', '3', '0' '3', '526', '4', '0' '3', '529', '10', '0' '3', '532', '5', '0' '3', '534', '9', '0' 

title

 id,   name       organization_id '165', 'head of it', '1' '166', 'webudvikler', '1' 

user

id, username, password, title_id, 1   **      , **       ,  165 2   **      , **      ,   165 3   **      , **     ,    166 


Comments

Popular posts from this blog

Payment information shows nothing in one page checkout page magento -

tcpdump - How to check if server received packet (acknowledged) -