how to select Count of Ranges from mysql table?


Problem :

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.



Solution :

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

Mysql Tutorials

Mysql Howto..

Mysql: how to add a virtual column before UNION

How to execute different query based on the result got from executing query in mysql?

How to display text in the same format as added in mysql text field using php echo

How would I count MYSQL rows down to a specific row in PHP?

How to Skip Records of MySQL table using PHP?

How can I change the grouping when displaying data from two joined tables?

How to fetch the last batch of updates from a MySQL database

How to refactor a single row insert query into multiple rows insert query in mysql?

How to select many database rows using custom rule?

How to compare two input Strings from mysql database table record.?

jdbc how to insert mysql SET type?

How to insert multiple rows from a table to another table based on date condition (PHP-MySQL-Query)?

How to Upload images to Php server and store in phpmyadmin

how to represent trees and their content in MySQL?

How to check condition before executing MYSQL query (Laravel 5)

How to have multiple replies in posting app?

How to convert a Coldfusion loop constructing MySQL synatx into a MySQL stored procedure?

How to properly pull out records constrained by a date calculation from a joined table in MySQL?

how to calculate unique column values in mysql

How to register mysql cursor in java?

How to display emaiIds with hyperlink when using php-mysql

How to set selected value in drop list in HTML?

how to update table based on another table in mysql?

How to MySQL Trigger to update a field whenever any record is touched?

How to display monthwise report in mysql

How can you insert at the end of an existing string filed a datafield with string type in MySQL kinda like CONCAT

How to tell if a MySQL field has a default value of an empty string or no default value?

How to select from joined MySQL tables?

how to search multi-categories in mysql

How do i calculate days / weeks past from current date to DateTime mysql field