Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to select Count of Ranges from mysql table?

i have a table t_points in mySql like example in below.

Name          Surname          Point
Joe           Arnold           120
Michale       Black            250
Masha         Petrova          300
Natalie       Jackson          120
John          Turo             200
Bona          Meseda           250
Zeyda         Nura             150
Zanura        Bohara           60
Shaheen       Boz              360
Abbas         Murat            160
Keira         Black            230
Tom           Robinson         480
Fred          Balka            490
Semia         Hudovi           90
Sona          Bahari           60

i want to write a query which will display the count of point ranges. Point ranges are like this: point between 0 and 100, 101 and 200, 201 and 300, 301 and 400. Result must be like below

0_100           101_200          201_300            301_400
3               5                4                  3

i think u understand what i want to say. So which query i have to use for this result?
Thanks.

like image 270
namco Avatar asked Apr 05 '12 12:04

namco


People also ask

How do I SELECT and count in MySQL?

MySQL COUNT syntax example As a part of the SELECT statement, the syntax mostly looks as below: SELECT COUNT(expression) FROM table WHERE condition; Expression is the parameter where you specify which values you want to count. It can be a field or string type value.

How do I count the number of data in MySQL?

To counts all of the rows in a table, whether they contain NULL values or not, use COUNT(*). That form of the COUNT() function basically returns the number of rows in a result set returned by a SELECT statement.

Can you list the ways to get the count of records in a table?

With the help of the SQL count statement, you can get the number of records stored in a table.


4 Answers

select
     count(CASE WHEN point BETWEEN 0 AND 100 THEN 1 END) as count0_100,
     count(CASE WHEN point BETWEEN 101 AND 200 THEN 1 END) as count101_200,
     count(CASE WHEN point BETWEEN 201 AND 300 THEN 1 END) as count201_300,
     ...
from
    t_poits
like image 139
gbn Avatar answered Oct 29 '22 10:10

gbn


Something like that:

select count(*) as count, abs(point/100) as range 
from t_poits
group by abs(point/100)
like image 22
Maksym Polshcha Avatar answered Oct 29 '22 10:10

Maksym Polshcha


set @range = 500;

select floor(field1/@range)*@range as `from`,(ceil(field1/@range)+if(mod(field1,@range)=0,1,0))*@range as `to`, count(field1)
from table1
group by 1,2
order by 1,2;
like image 2
venoel Avatar answered Oct 29 '22 11:10

venoel


You can group points column and do some math operation to specify range. Based on that you can count number of records.

Like..

SELECT concat( 101 * round( Point /101 ) , '-', 101 * round( Point /101 ) +100 ) AS `range` , count( * ) AS `result`
FROM t_points
GROUP BY 1
ORDER BY Point

I hope it will work for you.

like image 1
Ashish Jivani Avatar answered Oct 29 '22 12:10

Ashish Jivani