Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

this is the query:

$sql = "select, pn.purchased_number, pn.plan, pn.ring_to_group, 
        count( as totalGroups, 
        concat_ws(',', group_concat(rtg.group_name), group_concat( as groupList
           from tbl_phone_numbers as pn
           left join tbl_rings_to_groups as rtg on rtg.user_id=pn.user_id
           where pn.user_id = ? and pn.status = ?";
$query = $this->db->query($sql, array($this->userId, 'Y'));

that returns the result of groupList like default,demo,1,2 but i want to get result something like :


in MYSQL any suggestion??

share|improve this question
try group_concat(concat(rtg.group_name, ':', as groupList – ypercubeᵀᴹ May 1 '13 at 9:13
@ypercube, thanks i get the expected result with group_concat(concat_ws(':', rtg.group_name, as groupList, also tried yours and got the same result. Thanks again to responding. – jogesh_pi May 1 '13 at 9:23
@ypercube i want to close this question, so please reply in answer section so that this question also helpful to others. – jogesh_pi May 1 '13 at 9:25
up vote 1 down vote accepted

You can reverse the order of the two functions, so the concatenation of group_name and id is done first - using either CONCAT() or CONCAT_WS() - and then the group concatenation:

GROUP_CONCAT( CONCAT_WS(':', rtg.group_name, 
              ORDER BY
              SEPARATOR ','                      -- the comma ',' is the default
            ) AS groupList
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.