CODEWITHSUNDEEP
mysqlsql
Jason
Jason

Reputation: 373

mysql grouping a status field

I have a table

id, location, status
1, london, 1
2, london, 0
3, boston, 1
4, boston, 1

I'd like my query to generate something like this: -

location, status_1, status_0
london, 1, 1
boston, 2, 0

so far I have: -

select count(id) as freq, location from my_table
group by location order by freq desc;

I'm completely lost as to where to go from here.

Upvotes: 0

Views: 76

Answers (3)

Tom Mac
Tom Mac

Reputation: 9853

select location,
sum(case when status = 1 then 1 else 0 end) as status_1,
sum(case when status = 0 then 1 else 0 end) as status_0,
from my_table
group by location;

Upvotes: 1

GolezTrol
GolezTrol

Reputation: 116110

So you want to count the records for each status per city?

In the query below, I group by location (like your did), and then add a sum for each state. Inside the sum is a case, that either returns 1 if the record matches the desired state, or 0 otherwise. That way, you effectively count the number of records for each state per location.

select
  a.location,
  sum(case when a.status = 1 then 1 else 0 end) as status_1,
  sum(case when a.status = 0 then 1 else 0 end) as status_0
from
  YourTable a
group by
  a.location

Upvotes: 1

Marc B
Marc B

Reputation: 360692

That sort of transformation is better done in whatever client is issuing the query, but if you have to do it in the database, then 

select location,
    sum(status = 1) AS status_1,
    sum(status = 0) AS status_0
from my_table
group by location

it's a bit hackish, but the 'status = 1' when status really is 1 will evaluate to a boolean true, which MySQL will politely cast to an integer '1', which then gets summed up. Same goes for when status is 0 and status = 0 evaluates to true.

Upvotes: 1

Related Questions