mysql, cant group by without ruining my original code(inline view)-CodePudding

select ename as Name, sal as Salary, sal/sum(sal)*100 from stud_v22_lykkeboeale.emp

i have this code, but when i do group by sal, it converts from this:

| Name | Salary | sal/sum(sal)*100 |
|------ -------- ------------------|
|Smith |  800   |2.756244616709733 |

To this:

|Name   |salary | sal/sum(sal)*100|
|Smith  |800    |100|
|James  |950    |100|
|Adams  |1100   |100|
|Ward   |1250   |50 |
|Miller |1300   |100|
|Turner |1500   |100|
|Allen  |1600   |100|
|Clark  |2450   |100|
|Blake  |2850   |100|
|Jones  |2975   |100|
|Scott  |3000   |50 |
|King   |5000   |100|

It should output as this:

|Name  |    Salary |    sal/tot_sal*100    |
|------|-----------|-----------------------|
|Smith |    800    |    2.756244616709733  |
|James |    950    |    3.273040482342808  |
|Adams |    1100   |    3.7898363479758825 |
|Ward  |    1250   |    4.3066322136089585 |
|Martin|    1250   |    4.3066322136089585 |
|Miller|    1300   |    4.478897502153316  |
|Turner|    1500   |    5.167958656330749  |
|Allen |    1600   |    5.512489233419466  |
|Clark |    2450   |    8.440999138673558  |
|Blake |    2850   |    9.819121447028424  |
|Jones |    2975   |    10.249784668389319 |
|Ford  |    3000   |    10.335917312661499 |
|Scott |    3000   |    10.335917312661499 |
|King  |    5000   |    17.226528854435834 |

sal/tot_sal*100 is the % of the person salary compared to the sum of salary. It says in my assignment i should use inline view, but i dont know what that is, i have searched, yet havent found any "real" answer to what it is.

So if someone could help me, I'd appreciate it.

CodePudding user response：

Use window SUM(), not aggregate one:

SELECT Name, Salary, 100 * Salary / SUM(Salary) OVER () percent
FROM test
ORDER BY Salary, Name

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=99f39cbd06734643a64578aaccd36533