Search code examples
mysqlgroup-bygroup-concat

MySQL Group concat line separator

In my SQL query, I am joining two tables creatives and campaign and then using group_concat.

Below is my SQL query:

SELECT group_concat(creatives.creative_id) AS creative_ids, 
campaigns.id,creatives.creative_id, creatives.parentId, sum(creatives.views), sum(creatives.clicks)
FROM campaigns INNER JOIN creatives ON campaigns.id= creatives.parentId group by campaigns.id;

which produces result as below. You can see the two creative ids separated by comma: enter image description here

I want to separate the result for both creative_id as below:

enter image description here

I did some online research, but I didn't find any syntax for GROUP_CONCAT line separator. Is there any other solution for this problem? Any help is appreciated.

Solution

  • This query should solve your problem now that I understand it better. This JOINs creatives to a subquery which gives the total views and clicks for each campaign.

    SELECT creatives.creative_id AS creative_ids, ctotals.id, creatives.parentId, ctotals.views, ctotals.clicks
FROM creatives
JOIN (SELECT campaigns.id, campaigns.parentID, SUM(creatives.views) as views, SUM(creatives.clicks) as clicks
FROM creatives JOIN campaigns ON campaigns.id = creatives.parentId 
GROUP BY campaigns.id) ctotals on ctotals.parentID = creatives.parentID