I'm trying to add a new code to an already existing code that join 3 tables, but I'm having trouble running it. The goal is to add a table to the code that is producing a table. Here is the original code:
I have this code WHICH IS JOIN OF 3 TABLES,
SELECT
[Provider] AS Publisher
,[Price_Rate]
,[Source_ID]
,Media.Date
,Media.Impressions
,Media.Clicks
,'0' AS [Opt-buyers]
,'0' AS [Completed buyers]
,Media.accounting
FROM [dbo].[budget] AS BUDGET
LEFT JOIN
(
SELECT CASE WHEN [Ad_Set_Name] LIKE 'tw_%'
THEN'twitter'
WHEN [Ad_Set_Name] LIKE 'IN_%'
THEN 'insta'
ELSE '?'
END AS Publisher
,CAST([Day] AS Date) AS Date
,SUM(CAST([Impressions] AS INT)) AS Impressions
,SUM(CAST([Link_Clicks] AS INT)) AS Clicks
,SUM(CAST([Amount_Spent__USD_] AS money)) AS Spend
FROM [dbo].[twitter]
Group by Day,[Ad_Set_Name]
UNION ALL
SELECT CASE WHEN [Site__PCM_] = 'acuits.com'
THEN 'acqt'
WHEN [Site__PCM_]= 'PulsePoint'
THEN 'plpt'
WHEN [Site__PCM_] = 'SRAX'
THEN 'srax'
ELSE [Site__PCM_]
END AS Publisher
,CAST(Date AS Date) AS Date
,SUM(CAST(impressions AS INT)) AS Impressions
,SUM(CAST(clicks AS INT)) AS Clicks
,SUM(CAST(media_cost AS money)) AS Spend
FROM [dbo] [pcm]
Group by [Site__PCM_]
,Date
) AS new_sources_budget
ON BUDGET.Source_ID = Media.Publisher
WHERE source_id IS NOT NULL
and I'm trying to join another table **called Email** to what's this code is currently providing, but
I'm having tough time passing thus far. the goal is to add this code
SELECT
SUM(CAST(_Send2 AS INT)) AS [Email Sent]
,SUM(CAST(_Open2 AS INT)) AS [Email Open]
,SUM(CAST(Click2 AS INT)) AS [Email Click]
FROM [dbo].[behaviour]
Group by _Send2,_Open2,Click2
This code provide the following table
Publisher Source_ID Date Impression clicks optin Spend
accuea 1010 4/20/20 updown 10 yes 300
any help will be appreciated.
What I have tried:
I have tried to add this code to the current code but it doesn't work
[Provider] AS Publisher
,[Price_Rate]
,[Source_ID]
,Media.Date
,Media.Impressions
,Media.Clicks
,'0' AS [Opt-buyers]
,'0' AS [Completed buyers]
,Media.accounting
FROM [dbo].[budget] AS BUDGET
LEFT JOIN
(
SUM(CAST(_Send2 AS INT)) AS _Send2
,SUM(CAST([_Open2] AS INT)) AS _Open2
,SUM(CAST([Click2] AS money)) AS Click2
FROM [dbo].[Email]
Group by _Send2,_Open2,Click2
UNION ALL
SELECT CASE WHEN [Ad_Set_Name] LIKE 'tw_%'
THEN'twitter'
WHEN [Ad_Set_Name] LIKE 'IN_%'
THEN 'insta'
ELSE '?'
END AS Publisher
,CAST([Day] AS Date) AS Date
,SUM(CAST([Impressions] AS INT)) AS Impressions
,SUM(CAST([Link_Clicks] AS INT)) AS Clicks
,SUM(CAST([Amount_Spent__USD_] AS money)) AS Spend
FROM [dbo].[twitter]
Group by Day,[Ad_Set_Name]
UNION ALL
SELECT CASE WHEN [Site__PCM_] = 'acuits.com'
THEN 'acqt'
WHEN [Site__PCM_]= 'PulsePoint'
THEN 'plpt'
WHEN [Site__PCM_] = 'SRAX'
THEN 'srax'
ELSE [Site__PCM_]
END AS Publisher
,CAST(Date AS Date) AS Date
,SUM(CAST(impressions AS INT)) AS Impressions
,SUM(CAST(clicks AS INT)) AS Clicks
,SUM(CAST(media_cost AS money)) AS Spend
FROM [dbo] [pcm]
Group by [Site__PCM_]
,Date
) AS new_sources_budget
ON BUDGET.Source_ID = Media.Publisher
WHERE source_id IS NOT NULL
and I'm trying to join another table **called Email** Highlighted in Bold, but
I'm having tough time passing thus far. the goal is to add this code
When adding that the code highlighted in bold, I get the following error
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
SELECT
SUM(CAST(_Send2 AS INT)) AS [Email Sent]
,SUM(CAST(_Open2 AS INT)) AS [Email Open]
,SUM(CAST(Click2 AS INT)) AS [Email Click]
FROM [dbo].[behaviour]
Group by _Send2,_Open2,Click2