I am having issues unioning these three spend queries despite trying several different fixes including this version casting all variables.

SELECT inside.date AS "date", CAST('facebook' AS varchar(20)) AS "ad_channel", 
  CAST(inside.ad_customer_group AS varchar(3)) AS "ad_customer_group", CAST(SUM(inside.ad_spend) AS integer) AS "ad_spend"
FROM
(
  SELECT CAST(TRUNC(FP.date) AS date) AS "date", CASE WHEN UPPER(SUBSTRING(adset_name,12,3)) = 'RTN' THEN 'RTN' ELSE 'ACQ' END AS "ad_customer_group", 
    CAST(SUM(spend) AS integer) AS "ad_spend"
  FROM public.fact_facebook_paid_placements FP
  GROUP BY CAST(TRUNC(FP.date) AS date), FP.adset_name
  ORDER BY CAST(TRUNC(FP.date) AS date) DESC
) AS "inside"
GROUP BY inside.date, inside.ad_customer_group
ORDER BY inside.date DESC

UNION ALL

SELECT CAST(TRUNC(timestamp) AS date) AS "date", CAST('google_ads' AS varchar(20)) AS "ad_channel", 
  CAST('ACQ' AS varchar(3)) AS "ad_customer_group", CAST(SUM(cost)) AS integer) AS "ad_spend"
FROM public.fact_adwords_ads
GROUP BY CAST(TRUNC(timestamp) AS date)
ORDER BY CAST(TRUNC(timestamp) AS date) DESC

UNION ALL

SELECT CAST(TRUNC(timestamp) AS date) AS "date", CAST('bing_ads' AS varchar(20)) AS "ad_channel", 
  CAST('ACQ' AS varchar(3)) AS "ad_customer_group", CAST(SUM(spend)) AS integer) AS "ad_spend"
FROM public.fact_bing_ad_performance
GROUP BY CAST(TRUNC(timestamp) AS date)
ORDER BY CAST(TRUNC(timestamp) AS date) DESC

CodePudding user response：

It'd be a great help if you post an error which you got along the query. But what I can assume from your query is that you have gotten error about GROUP BY statement as you grouped only by a first column (and second in one of the queries), and didn't use anothers in group by or in withing any aggregation functions

So, I guess this query will do what you want

(
SELECT
  CAST(TRUNC(FP.date) AS date) AS "date",
  CAST(CASE
    WHEN UPPER(SUBSTRING(adset_name,12,3)) = 'RTN' THEN 'RTN'
    ELSE 'ACQ'
  END AS varchar(3)) AS "ad_customer_group",
  SUM(spend) AS "ad_spend"
FROM public.fact_facebook_paid_placements FP
GROUP BY 1, 2, 3
ORDER BY 1 DESC
)

UNION ALL

(
SELECT
  CAST(TRUNC(timestamp) AS date) AS "date",
  CAST('google_ads' AS varchar(20)) AS "ad_channel",
  CAST('ACQ' AS varchar(3)) AS "ad_customer_group",
  SUM(cost) "ad_spend"
FROM public.fact_adwords_ads
GROUP BY 1, 2, 3
ORDER BY 1 DESC
)

UNION ALL

(
SELECT
  CAST(TRUNC(timestamp) AS date) AS "date", 
  CAST('bing_ads' AS varchar(20)) AS "ad_channel", 
  CAST('ACQ' AS varchar(3)) AS "ad_customer_group", 
  SUM(spend) AS "ad_spend"
FROM public.fact_bing_ad_performance
GROUP BY 1, 2, 3
ORDER BY 1 DESC
)