1

This query is close to what I need but google_responsive_headlines.responsive_headlines has many rows to google_text_ads. I would like to have those results in the same row but not sure how to achieve that. Appreciate any advice!

    SELECT google_responsive_headlines.responsive_headlines,
        google_responsive_headlines.responsive_path_1,
        google_responsive_headlines.responsive_path_2,
        d.responsive_descriptions,
        s.clicks,
        s.cost,
        s.impressions,
        s.ad_id,
        s.status,
        s.final_url,
        s.display_url
    FROM
        irene_db.google_responsive_descriptions d
            INNER JOIN
                (SELECT 
                    SUM(clicks) AS clicks,
                        SUM(cost) AS cost,
                        SUM(impressions) AS impressions,
                        google_text_ads.ad_id,
                        google_text_ads.status,
                        google_text_ads.final_url,
                        google_text_ads.display_url
                FROM
                    google_text_ads
                WHERE
                    google_text_ads.customer_id = 144
                        AND (google_text_ads.date >= '2022-05-21'
                        AND google_text_ads.date <= '2022-06-21')
                        AND google_text_ads.status IN ('ENABLED')
                        AND google_text_ads.type IN ('EXPANDED_TEXT_AD' , 'RESPONSIVE_SEARCH_AD')
                GROUP BY ad_id) s ON s.ad_id = d.ad_id
        INNER JOIN
        irene_db.google_responsive_headlines ON s.ad_id = google_responsive_headlines.ad_id
            AND google_responsive_headlines.customer_id = 144
            AND (google_responsive_headlines.date >= '2022-05-21'
            AND google_responsive_headlines.date <= '2022-06-21')
            group by google_responsive_headlines.responsive_headlines
    LIMIT 50;

Here's my results...

enter image description here

How can I get responsive_headlines to be in separate columns and have the query return 1 row?

EDIT: The query result is the same in every other column except responsive_headline. There can be up to 15 of those per 1 row in google_text_ads. So is there anyway in my return query I can have responsive_headline1, responsive_headline2,... responsive_headline15.

    responsive_headlines    responsive_path_1   responsive_path_2   responsive_descriptions clicks  cost    impressions ad_id   status  final_url   display_url customer_id date    type
    Retirement Plan Administration, RetirementPlan, Administration, Partner with[company name] to offer workplace retirement plan administration to your clients., 39, 133440000, 1332, [ad_id int], ENABLED, [weburl], , 144, 2022-05-21, RESPONSIVE_SEARCH_AD
    We Help Financial Planners, RetirementPlan, Administration, Partner with[company name] to offer workplace retirement plan administration to your clients., 39, 133440000, 1332, [ad_id int], ENABLED, [weburl], , 144, 2022-05-21, RESPONSIVE_SEARCH_AD
    We Work W/ Financial Planners, RetirementPlan, Administration, Partner with[company name] to offer workplace retirement plan administration to your clients., 39, 133440000, 1332, [ad_id int], ENABLED, [weburl], , 144, 2022-05-21, RESPONSIVE_SEARCH_AD
New contributor
Andrew Kloos is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
0

1 Answer 1

1

You can first take a row-number, then conditionally aggregate (pivot) over that.

The joins and grouping/partitioning columns are unclear, you may need to modify.

SELECT
    grh.*
    d.responsive_descriptions,
    s.clicks,
    s.cost,
    s.impressions,
    s.ad_id,
    s.status,
    s.final_url,
    s.display_url
FROM
    irene_db.google_responsive_descriptions d
INNER JOIN (
    SELECT 
        SUM(gta.clicks) AS clicks,
        SUM(gta.cost) AS cost,
        SUM(gta.impressions) AS impressions,
        gta.ad_id,
        gta.status,
        gta.final_url,
        gta.display_url
    FROM
        google_text_ads gta
    WHERE gta.customer_id = 144
      AND gta.date >= '2022-05-21'
      AND gta.date <= '2022-06-21'
      AND gta.status IN ('ENABLED')
      AND gta.type IN ('EXPANDED_TEXT_AD' , 'RESPONSIVE_SEARCH_AD')
    GROUP BY gta.ad_id  -- there should be other non-aggregated columns here
) s ON s.ad_id = d.ad_id
INNER JOIN (
    SELECT
        grh.adid,
        grh.responsive_path_1,
        grh.responsive_path_2,
        grh.responsive_descriptions,
        MAX(CASE WHEN grh.rn = 1  THEN grh.responsive_headlines END) responsive_headlines1,
        MAX(CASE WHEN grh.rn = 2  THEN grh.responsive_headlines END) responsive_headlines2,
        MAX(CASE WHEN grh.rn = 3  THEN grh.responsive_headlines END) responsive_headlines3,
        MAX(CASE WHEN grh.rn = 4  THEN grh.responsive_headlines END) responsive_headlines4,
        MAX(CASE WHEN grh.rn = 5  THEN grh.responsive_headlines END) responsive_headlines5,
        MAX(CASE WHEN grh.rn = 6  THEN grh.responsive_headlines END) responsive_headlines6,
        MAX(CASE WHEN grh.rn = 7  THEN grh.responsive_headlines END) responsive_headlines7,
        MAX(CASE WHEN grh.rn = 8  THEN grh.responsive_headlines END) responsive_headlines8,
        MAX(CASE WHEN grh.rn = 9  THEN grh.responsive_headlines END) responsive_headlines9,
        MAX(CASE WHEN grh.rn = 10 THEN grh.responsive_headlines END) responsive_headlines10,
        MAX(CASE WHEN grh.rn = 11 THEN grh.responsive_headlines END) responsive_headlines11,
        MAX(CASE WHEN grh.rn = 12 THEN grh.responsive_headlines END) responsive_headlines12,
        MAX(CASE WHEN grh.rn = 13 THEN grh.responsive_headlines END) responsive_headlines13,
        MAX(CASE WHEN grh.rn = 14 THEN grh.responsive_headlines END) responsive_headlines14,
        MAX(CASE WHEN grh.rn = 15 THEN grh.responsive_headlines END) responsive_headlines15
    FROM (
        SELECT
            grh.*,
            ROW_NUMBER() OVER (
                PARTITION BY
                  grh.ad_id,
                  grh.responsive_path_1,
                  grh.responsive_path_2,
                  grh.responsive_descriptions
                ORDER BY grh.date) rn
        FROM
            irene_db.google_responsive_headlines grh
        WHERE grh.customer_id = 144
          AND grh.date >= '2022-05-21'
          AND grh.date <= '2022-06-21'
    ) grh
    GROUP BY
        grh.responsive_headlines,
        grh.responsive_path_1,
        grh.responsive_path_2,
        grh.responsive_descriptions
) grh ON s.ad_id = grh.ad_id
LIMIT 50;

On older versions of MySQL, you need to aggregate them all up and use SUBSTRING_INDEX to pull out each one

SELECT
    grh.*
    d.responsive_descriptions,
    s.clicks,
    s.cost,
    s.impressions,
    s.ad_id,
    s.status,
    s.final_url,
    s.display_url
FROM
    irene_db.google_responsive_descriptions d
INNER JOIN (
    SELECT 
        SUM(gta.clicks) AS clicks,
        SUM(gta.cost) AS cost,
        SUM(gta.impressions) AS impressions,
        gta.ad_id,
        gta.status,
        gta.final_url,
        gta.display_url
    FROM
        google_text_ads gta
    WHERE gta.customer_id = 144
      AND gta.date >= '2022-05-21'
      AND gta.date <= '2022-06-21'
      AND gta.status IN ('ENABLED')
      AND gta.type IN ('EXPANDED_TEXT_AD' , 'RESPONSIVE_SEARCH_AD')
    GROUP BY gta.ad_id  -- there should be other non-aggregated columns here
) s ON s.ad_id = d.ad_id
INNER JOIN (
    SELECT
        grh.adid,
        grh.responsive_path_1,
        grh.responsive_path_2,
        grh.responsive_descriptions,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 1) responsive_headlines1,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 2) responsive_headlines2,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 3) responsive_headlines3,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 4) responsive_headlines4,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 5) responsive_headlines5,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 6) responsive_headlines6,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 7) responsive_headlines7,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 8) responsive_headlines8,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 9) responsive_headlines9,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 10) responsive_headlines10,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 11) responsive_headlines11,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 12) responsive_headlines12,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 13) responsive_headlines13,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 14) responsive_headlines14,
        SUBSTRING_INDEX(GROUP_CONCAT(grh.responsive_headlines '~|~'), '~|~', 15) responsive_headlines15
    FROM
        irene_db.google_responsive_headlines grh
    WHERE grh.customer_id = 144
      AND grh.date >= '2022-05-21'
      AND grh.date <= '2022-06-21'
    GROUP BY
        grh.responsive_headlines,
        grh.responsive_path_1,
        grh.responsive_path_2,
        grh.responsive_descriptions
) grh ON s.ad_id = grh.ad_id
LIMIT 50;
3
  • I am on mysql 5.7.25... Jun 23 at 12:31
  • OK given you a solution for that also Jun 23 at 12:43
  • Thanks!! We got it running! Jun 23 at 20:02

Your Answer

Andrew Kloos is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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