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

Post a Comment

Popular posts from this blog

How to run C# code using mono without Xamarin in Android? -

-
i want create program uses mono runtime run c# code (binary not application). i copied libmono.so unity app , i'm write code using documentation: http://www.mono-project.com/docs/advanced/embedding/ and it's working have problems references can access classes mscorlib.dll , have problems system.reflection.assembly class throwing exception, when print console see: system.missingmethodexception: method not found: 'system.reflection.assembly.op_inequality' can fix it? can use mono runtime in c binary on android? ps. i'm using android tests. edit: found solution loading libraries, need set environment variable mono_path libraries path. but have still problems assembly class. the system.missingmethodexception error have seems .net profile mismatch error, 1 explained here . can use mono runtime in c binary on android? yes, using ndk guess. there alternatives can use instead of rolling own, such xmadevlab .
Read more

html - grunt SVG to webfont -

-
i'm trying generate webfont svgs using grunt-webfont . the issue need task valid on windows , linux machines. in order achieve need use node engines doesn’t work svg files . i know if there way convert svgs proper svg format supported grunt plugin or alternatively there better grunt plugin/s achieve this i have task able take svgs (created in illustrator) , output webfont files (.eot,.svg,.svg,...) , css file (like icomoon export) thanks. perhaps artboard small. recommend checking have 512pt x 512pt artboard each icon. stumbled upon gem ( https://www.fourdigits.nl/blog/using-a-webfont-to-display-icons ) when grunt-webfont documentation left me alone in forest.
Read more

python - Specify path of savefig with pylab or matplotlib -

-
i struggling on how find correct way specify save path (or repository) when calling function savefig in matplotlib or pylab. i tried several syntaxes, everytime python console returns : filenotfounderror: [errno 2] no such file or directory: '../mydocs/resource/frames/myimage.png' currently, have written following : pylab.savefig('../mydocs/resource/frames/myimage.png') does know how it? thanks in advance! the tilde operator , variable $home entered strings , therefore not function while saving. have provide either relative path (as did) or provide full path. eg. pylab.savefig("/home/username/desktop/myfig.png") .
Read more