Syntax to run a distinct GROUP_CONCAT in Google Bigquery

google-bigquery

Solution

Use a subquery to group by and get distinct values. something like this:

SELECT campaign.id AS campaign_id,
       GROUP_CONCAT(utm.campaign) AS utm_campaign
FROM
    (Select campaign.id,utm.campaign
    FROM [email_event]
    WHERE (TIMESTAMP BETWEEN SEC_TO_TIMESTAMP(1412136000) AND SEC_TO_TIMESTAMP(1414814340))
    GROUP EACH BY campaign.id,utm.campaign)
    GROUP BY campaign_id;

another option for few aggregated fields is to do it in stages...

SELECT campaign_id ,
       GROUP_CONCAT(utm_campaign) as utm_campaign,
       utm_content
       From
(SELECT campaign.id AS campaign_id,
       utm.campaign as utm_campaign,
       GROUP_CONCAT(utm.content) AS utm_content
FROM
    (
SELECT *
FROM
  ( SELECT 507 AS campaign.id,
           'remarketingemail' AS utm.campaign,
           'newsletter_feb' AS utm.content),
  ( SELECT 507 AS campaign.id,
           'remarketingemail2' AS utm.campaign,
           'newsletter_feb' AS utm.content),
  (SELECT 508 AS campaign.id,
          'remarketingemail' AS utm.campaign,
          'newsletter_jan' AS utm.content),
  (SELECT 508 AS campaign.id,
          'remarketingemail' AS utm.campaign,
          'newsletter_feb' AS utm.content)
      )
    GROUP BY utm_campaign,campaign_id)
    GROUP BY utm_content,campaign_id
    ;

Problem

I have this query: ``` SELECT campaign.id AS campaign_id, GROUP_CONCAT(utm.campaign) AS utm_campaign FROM [email_event] WHERE (TIMESTAMP BETWEEN SEC_TO_TIMESTAMP(1412136000) AND SEC_TO_TIMESTAMP(1414814340)) GROUP BY campaign_id; ``` And I would love to run a distinct GROUP_CONCAT, as now same entries are repeated in the output. UPDATE I've extended your solution to this: ``` SELECT campaign.id AS campaign_id, GROUP_CONCAT(utm.campaign) AS utm_campaign, GROUP_CONCAT(utm.content) AS utm_content FROM (SELECT * FROM (SELECT 507 AS campaign.id, 'remarketingemail' AS utm.campaign, 'newsletter_feb' AS utm.content), (SELECT 508 AS campaign.id, 'remarketingemail' AS utm.campaign, 'newsletter_jan' AS utm.content), (SELECT 508 AS campaign.id, 'remarketingemail' AS utm.campaign, 'newsletter_feb' AS utm.content), (SELECT 508 AS campaign.id, 'adwordscamp' AS utm.campaign, 'cyber_monday' AS utm.content) ) GROUP BY campaign_id; ``` But now I got repeated values for utm_campaign. ``` +-----+------------------------------------------+--------------------------------------+ | 507 | remarketingemail | newsletter_feb | | 508 | remarketingemail,remarketingemail,adw... | newsletter_jan,newsletter_feb,cyb... | +-----+------------------------------------------+--------------------------------------+ ``` this is the raw output on the subquery, before qroup by ``` +-----+-----------------------------------+-------------------------------+ | 507 | remarketingemail | newsletter_feb | | 508 | remarketingemail | newsletter_jan | | 508 | remarketingemail | newsletter_feb | | 508 | adwordscamp | cyber_monday | +-----+-----------------------------------+-------------------------------+ ```

Original source