CODEWITHSUNDEEP
sqlgoogle-bigquery
Goeast
Goeast

Reputation: 71

SQL Combine multiple different rows into one row

Here is my query :

SELECT
      basic_all_levels.date,
      basic_all_levels.account_id,
      basic_all_levels.ad_id,
      basic_all_levels.inline_link_clicks AS link_clicks,
      basic_all_levels.reach,
      basic_all_levels.spend AS cost,
      fb_basic_all_levels_cost_per_action_type.action_type,
      fb_basic_all_levels_cost_per_action_type.value,
      CASE WHEN (spend > 0) AND action_type = 'landing_page_view'
          THEN spend / value
          ELSE 0 END AS landing_page_views,
      CASE WHEN (spend > 0) AND action_type = 'offsite_conversions_fb_pixel_purchase'
          THEN  spend / value
          ELSE 0 END AS purchases,


  FROM `x.facebook_ad_insights.fb_basic_all_levels` AS basic_all_levels
  LEFT JOIN `x.facebook_ad_insights.fb_basic_all_levels_cost_per_action_type` AS fb_basic_all_levels_cost_per_action_type
      ON basic_all_levels.ad_id = fb_basic_all_levels_cost_per_action_type.ad_id and basic_all_levels.date = fb_basic_all_levels_cost_per_action_type.date

Here is my result

enter image description here

I want a only one row like the following :

enter image description here

I don't need the "value" column because i can recalculate it.

I have looked in many questions but I don't understand how to aggregate because : - group by does not work. It says that : "landing page view" is neither grouped nor aggregated - I checked about my "Left Join" and tried so see if something more appropriate existed - I saw a pivot function but I want to select only specific values (landing page views and purchase and not all listed in action types columns.

Upvotes: 0

Views: 125

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173210

I would go with below version (BigQuery Standard SQL) 

#standardSQL
SELECT
  basic_all_levels.date,
  basic_all_levels.account_id,
  basic_all_levels.ad_id,
  basic_all_levels.inline_link_clicks AS link_clicks,
  basic_all_levels.reach,
  basic_all_levels.spend AS cost,
  IF(landing_page_views > 0, spend / landing_page_views, 0) AS landing_page_views,
  IF(purchases > 0, spend / purchases, 0) AS purchases
FROM `x.facebook_ad_insights.fb_basic_all_levels` AS basic_all_levels
LEFT JOIN (
  SELECT
    ad_id,
    `date`,
    SUM(IF(action_type = 'landing_page_view', value, 0)) AS landing_page_views,
    SUM(IF(action_type = 'offsite_conversions_fb_pixel_purchase', value)) AS purchases
  FROM `x.facebook_ad_insights.fb_basic_all_levels_cost_per_action_type`
  GROUP BY ad_id, `date`
) AS fb_basic_all_levels_cost_per_action_type
USING(ad_id, `date`)

Should be much more optimal to compare with version of first JOIN'ing and then GROUP'ing

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271231

I think you are looking for aggregation. As best as I can figure out:

SELECT bal.date, bal.account_id, bal.ad_id,
       spend as cost,
       SUM(CASE WHEN spend > 0 AND action_type = 'landing_page_view'
                THEN spend / value ELES 0
           END) AS landing_page_views,
       SUM(CASE WHEN spend > 0 AND action_type = 'offsite_conversions_fb_pixel_purchase'
                THEN  spend / value ELSE 0
           END) AS purchases
FROM `x.facebook_ad_insights.fb_basic_all_levels` bal LEFT JOIN
     `x.facebook_ad_insights.fb_basic_all_levels_cost_per_action_type` balcat       
     ON bal.ad_id = balcat.ad_id AND
        bal.date = balcat.date
GROUP BY bal.date, bal.account_id, bal.ad_id, spend;

Upvotes: 0

Related Questions