Reputation: 4597
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
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
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