I have a table and would like to count the appearance of the values (no sum)
ID status
============
1 2
2 3
3 1
4 1
5 1
6 2
and I need following result:
status_1 status_2 status_3 status_4
===================================
3 2 1 0
Can I do this with a single SQL statement?
select
sum(if(status=1,1,0)) as status_1,
sum(if(status=2,1,0)) as status_2,
sum(if(status=3,1,0)) as status_3,
sum(if(status=4,1,0)) as status_4
from foo
Use a group query, with a count function:
select status, count(status)
from tablename
group by status
Select
sum(case status When 1 Then 1 End) status1,
sum(case status When 2 Then 1 End) status2,
sum(case status When 3 Then 1 End) status3,
sum(case status When 4 Then 1 End) status4,
etc.
From table
else 0 to match the output in the question.
CASE over IF because it's more "standard", but your answer will show NULL for status4, whereas the OP wants it to be zero.Is important to notice that what you are asking would imply a code modification as the supported status change. I think it could be better to have the result in several lines. This one could give you the same resuts in several rows:
SELECT COUNT(DISTINCT status) FROM mytable;
So you can iterate over the hole time, without query modifications. Hope that helps
Try this one will help you
select status, count(*) from tablename
group by status
select count(status) where status = x
Would yield the count for a single status.
Perhaps you could nest this statement but that may be inefficient.
Edit: Group By is the way to go. See other answer.
