elreeda
elreeda

Reputation: 4597

Select count(*)... mysql

I have a table like this one :

id : name : point 
 1   john      30
 2   james     30
 3   owen      20
 4   mikel     20
 5   jenny     20
 6   kobi      10

how I can use the count() function to select * who have point like 20 and less than 20 and > 20

select (*) from table_name where point like 20 // return 3
select (*) from table_name where point < 20 // return 1
select (*) from table_name where point > 20 // return 2

how I can those 3 query in one to return a table

Count(*)
________
   3
   1
   2

Upvotes: 1

Views: 1863

Answers (2)

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

select * from(select count(id) from table_name where point = 20
union
select count(id) from table_name where point < 20 
union
select count(id) from table_name where point > 20 
)a

try to this

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Use conditional aggregation:

select sum(point = 20),
       sum(point < 20),
       sum(point > 20)
from table_name;

Also, don't use like with numbers! It converts the values to strings. That could produce unexpected results and might make the query unable to use indexes.

Upvotes: 3

Related Questions