Returning label strings in groups of 10, 20, etc.

Question

I have a table of numbers and placemarks:

seat marks 61 45 62 25 63 45 64 23 65 25 66 9 67 23 

The max sign is 100. Now I want to show how many candidates fix tags at 10, 20, 30, ... 100 s

 marks candidates_count 10 1 20 4 30 0 .. .. 

And so on. Now i know it

SELECT seat, marks, count(marks) as counts from <table> group by marks order by counts desc;

or do it for every 10, 20 and 30 s

SELECT seat, marks from <table> where marks>10 and marks<=20 group by marks;

and get the number of rows returned in my php and return the results, but this is not very elegant. There must be a way to do this directly in MySQL and without using MySQL for loops.

Answer 1

The following will definitely work for you

 select (FLOOR(`marks`/ 10)+1)*10 as marks, count(*) as candidates_count from <table> group by (FLOOR(`marks`/ 10)+1)*10; 

Answer 2

try the following:

 select (marks/10)*10 as marks, count(*) as candidates_count from <table> group by (marks/10)*10 

Answer 3

Try:

 select ((marks/10)+1)*10, count(1) from tab group by ((marks/10)+1)*10 

But if you want to see 0 , you have to prepare the expected labels:

 create table tab2 ( marks int ) insert into tab2 values(10) insert into tab2 values(20) insert into tab2 values(30) insert into tab2 values(40) insert into tab2 values(50) insert into tab2 values(60) .... insert into tab2 values(100) 

and use right join :

 select t2.marks, count(tab.marks) from tab right join tab2 t2 on t2.marks = ((tab.marks/10)+1)*10 group by t2.marks order by 1