COUNT and GROUP_CONCAT in mysql

Question

I am having three tables pcn_type, in_e_s_s__p_c_ns, p_c_n_details. I am trying to concat three different values into single using group concat.

My Query:

SELECT 'browser' AS NAME, CONCAT( '[', CONCAT('{"', pcn_type.name, '",', 
COUNT(JPN_ID), '}'), ']' ) AS DATA FROM p_c_n_details INNER JOIN 
in_e_s_s__p_c_ns RIGHT OUTER JOIN pcn_type ON pcn_type.name = 
p_c_n_details.type AND in_e_s_s__p_c_ns.pcnid= 
p_c_n_details.JPN_ID GROUP BY pcn_type.name

Result got:

NAME    |    DATA
-------------------------------------
browser      [{"Design Change",4}]
browser      [{"EOL",10}]
browser      [{"Process Change",21}]

Expecting Result:

NAME    |    DATA
--------------------------------------------------------------------
browser      [{"Design Change",4},{"EOL",10},{"Process Change",21}]

How to restructure the above query to get the expected result.

If available, I would build the jsonish string in some kind of application code. — Strawberry
– Strawberry, Commented Aug 6, 2018 at 13:26

Zaynul Abadin Tuhin · Accepted Answer · 2018-08-06 12:54:09Z

1

use GROUP_CONCAT function

 select name,GROUP_CONCAT(DATA  SEPARATOR ' ') 
from 
(          
    SELECT 'browser' AS NAME, CONCAT( '[', CONCAT('{"', pcn_type.name, '",', 
    COUNT(JPN_ID), '}'), ']' ) AS DATA FROM p_c_n_details INNER JOIN 
    in_e_s_s__p_c_ns RIGHT OUTER JOIN pcn_type ON pcn_type.name = 
    p_c_n_details.type AND in_e_s_s__p_c_ns.pcnid= 
    p_c_n_details.JPN_ID GROUP BY pcn_type.name
) as T group by name

answered Aug 6, 2018 at 12:54

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

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

1 Comment

Kannan K Over a year ago

Thanks you Zaynul.

Collectives™ on Stack Overflow

COUNT and GROUP_CONCAT in mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related