GROUP_CONCAT with two fields

Question

My table has two fields (ID and Status)

I'd like to get a group_concat of the count of each status for each ID.

ID  Status
1   True
1   True
1   False
2   True
2   False
2   False

I'd like my results to be

ID Group_Concat
1  (True-2, False-1)
2  (True-1, False-2)

I can parse the True-# / False-# in PHP...unless there's a way to make that a separate field in the group_concat?

I've played with the following, but to no avail..it keeps making a separate row for each ID and Status. This will be part of a larger SELECT but I don't think it's relevant to clutter this question up with that SQL.

SELECT ID,Status, GROUP_CONCAT(c) 
FROM (
  SELECT ID, Status,COUNT(Status) c 
  FROM buyer_advert GROUP BY ID,Status
) a GROUP BY ID, Status

jpw · Accepted Answer · 2015-08-01 23:22:33Z

5

You can do this by adding a concat inside the group_concat like this:

SELECT 
  ID,
  GROUP_CONCAT(CONCAT(Status,'-',c),' ' ORDER BY Status DESC) 
FROM (SELECT ID, status, COUNT(Status) c
      FROM buyer_advert 
      GROUP BY ID, status
     ) a 
GROUP BY ID;

Sample SQL Fiddle

answered Aug 1, 2015 at 23:22

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bohemian · Accepted Answer · 2015-08-01 23:49:54Z

1

Use a subquery:

select id, group_concat(concat(status, '-', cnt))
from (select id, status, count(*) cnt
  from mytable group by 1, 2) x
group by 1

See SQLFiddle

edited Aug 1, 2015 at 23:49

answered Aug 1, 2015 at 23:22

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Collectives™ on Stack Overflow

GROUP_CONCAT with two fields

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related