0

I am making charts for my mysql database. In order to use Bar Chart to display some data as numeric distributions, I have to aggregate a table so it will be like this:

select user_score, count(*) as cnt from t group by user_score order by user_score;

|user_score| cnt |
|         1| 1000|
|         2|  300|
|         5|  500|
|         7|   10|
|       ...|  ...|

We assume that the domain of user_score is all integers from 1 to 100. I want the result to be:

|user_score| cnt |
|         1| 1000|
|         2|  300|
|         3|    0|
|         4|    0|
|         5|  500|
|         6|    0|
|         7|   10|
|       ...|  ...|
|       100|    0|

this question may also look like:

aggregation result:

|  time | count |
|  1:00 |     10|
|  4:00 |      1|

expected result:

|  time | count |
|  1:00 |     10|
|  2:00 |      0|
|  3:00 |      0|
|  4:00 |      1|
|   ... |    ...|
| 24:00 |      0|

Any ideas?

Rick Dou
  • 154
  • 9
  • Not voting to close because I don't want to dupehammer it, but I think this should do the trick: https://stackoverflow.com/q/6870499/2422776 – Mureinik Dec 25 '22 at 14:07
  • The solution depends on your MySQL version. Show the output for `SELECT VERSION();`. – Akina Dec 25 '22 at 19:14

0 Answers0