Counting rows in mysql database

Question

I want to count from the row with the least value to the row with a specific value. For example,

Name / Point
--------------------
Pikachu  / 7
Voltorb / 1
Abra / 4
Sunflora / 3
Squirtle / 8
Snorlax / 12

I want to count to the 7, so I get the returned result of '4' (counting the rows with values 1, 3, 4, 7)

I know I should use count() or mysql_num_rows() but I can't think of the specifics. Thanks.

score 2 · Answer 1 · answered Jun 11 '12 at 18:02

2

I think you want this :

 select count(*) from mytable where Point<=7;

Count(*) counts all rows in a set.

answered Jun 11 '12 at 18:02

Denys Séguret

Stephen · Accepted Answer · 2012-06-11T18:12:15.297

1

If you're working with MySQL, then you could ORDER BY Point:

SELECT count(*) FROM table WHERE Point < 7 ORDER BY Point ASC

If you want to know all about ORDER BY, check out the w3schools page: http://www.w3schools.com/sql/sql_orderby.asp

Just in case you want to only count the rows based on the Point values:

SELECT count(*) FROM table WHERE Point < 7 GROUP BY Point

edited Jun 11 '12 at 18:12

answered Jun 11 '12 at 18:03

Stephen

"row with the least value to the row with a specific value" makes me think that he wants to make sure that there is some order in the Point values, not just in this specific case where they properly align. – Stephen Jun 11 '12 at 18:07
2

If you return only the row count, the order is useless. – Denys Séguret Jun 11 '12 at 18:07

score 1 · Answer 3 · answered Jun 11 '12 at 18:09

1

This may help you to get rows falling between range of values :

select count(*) from table where Point >= least_value and Point<= max_value

answered Jun 11 '12 at 18:09

Ravi Jain

3 Answers3