CODEWITHSUNDEEP
mysqlgroup-concat
Kannan K
Kannan K

Reputation: 4461

COUNT and GROUP_CONCAT in mysql

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.

Upvotes: 0

Views: 51

Answers (1)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

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

Upvotes: 1

Related Questions