SQL joining a separate query as a column in original query

Question

I am struggling with joining the below two queries.

My main query is the first one below and what I am trying to achieve is the output of query 2's opt in rate column as a new column in my original query.

SELECT CAST("public"."event_event"."date_created" AS date) AS "date_created", "marketing_message__via__messag"."name" AS "name", count(*) AS "count"

FROM "public"."event_event"

LEFT JOIN "public"."marketing_message" "marketing_message__via__messag" ON "public"."event_event"."message_id" = "marketing_message__via__messag"."id" LEFT JOIN "public"."marketing_campaign" "marketing_campaign__via__campa" ON "public"."event_event"."campaign_id" = "marketing_campaign__via__campa"."id"

WHERE (date_trunc('month', CAST("public"."event_event"."date_created" AS timestamp)) = date_trunc('month', CAST(now() AS timestamp))
   AND "marketing_message__via__messag"."name" IS NOT NULL AND ("marketing_message__via__messag"."name" <> ''
    OR "marketing_message__via__messag"."name" IS NULL) AND "public"."event_event"."stage" = 'Lead' AND ("marketing_campaign__via__campa"."name" = 'a' 
OR "marketing_campaign__via__campa"."name" = 'b'
OR "marketing_campaign__via__campa"."name" = 'c' 
OR "marketing_campaign__via__campa"."name" = 'c1' OR "marketing_campaign__via__campa"."name" = 'd' 
OR "marketing_campaign__via__campa"."name" = 'e'))

GROUP BY CAST("public"."event_event"."date_created" AS date), "marketing_message__via__messag"."name"

ORDER BY CAST("public"."event_event"."date_created" AS date) ASC, "marketing_message__via__messag"."name" ASC

I would like to add the following query output for "Opt-In rate" to my query above as a new column.

Query 2

SELECT marketing_message.message_text,
   
    cast(sum((event_event.status='Opt-in')::int) as decimal) / nullif(sum((event_event.status='Sent')::int), 0)* 100 as "Opt-in Rate (Sent)"
    
FROM event_event
JOIN marketing_campaign ON event_event.campaign_id = marketing_campaign.id
JOIN marketing_message ON event_event.message_id = marketing_message.id
WHERE True [[AND {{campaign_name}}]] [[AND {{date_created}}]]
GROUP BY marketing_message.message_text

score 0 · Accepted Answer · answered Feb 12 '21 at 11:06

The short answer is that you can't.

Your first query is keyed on date and name (the GROUP BY clause) and your second query is keyed on message_text. As there is no relationship between the two datasets there is no way of joining/combining them in a single query.

You would need to find a common field (or fields) between the two datasets and join on them - but this won't give the same results that you have at the moment as your queries would need to be completely restructured.

SQL joining a separate query as a column in original query

1 Answers1