What is the query to make a grouping like this?-CodePudding

I have a table that look like this:

| order_id | qty |

| -------- | --- |

| 00001 | 27 |

| 00002 | 17 |

I want to make something like this

| order_id | qty | 20 | 5 | 3 | 1 |

| -------- | --- | -- | - | - | - |

| 00001 | 27 | 1 | 1 | 0 | 2 |

| 00002 | 17 | 0 | 3 | 0 | 2 |

Has anyone know query to make a grouping like this? Thanks in advance

CodePudding user response：

working out each column by reduction (for want of a better word)

SET @N = 18;
SELECT FLOOR(@N/20) '20',
         FLOOR((@N-(FLOOR(@N/20)*20))/5) '5',
         FLOOR((@N-((FLOOR(((FLOOR(@N/20)*20))))  
          ((FLOOR((@N-(FLOOR(@N/20)*20))/5)) *5)) 
         ) / 3) '3'
         ;