0

I have a question involving these two tables:

marketing_campaign

| date    | campaign  | spend |
| ------- | ------    | ----- |
| 3/1/2020|   X       |  200  | 
| 3/1/2020|   Y       |  200  |
| 3/2/2020|   X       |  200  | 
| 3/2/2020|   X       |  200  |
| 3/3/2020|   Y       |  200  |

new_customers

| date    | campaign  | customer_id |
| ------- | ------    | ----------- |
| 3/1/2021|   X       |      1      | 
| 3/1/2021|   X       |      2      |
| 3/2/2021|   X       |      3      | 
| 3/2/2021|   X       |      4      |
| 3/2/2021|   Y       |      5      | 
| 3/3/2021|   X       |      6      |
| 3/3/2021|   Y       |      7      | 
| 3/3/2021|   Y       |      8      |

I needed to create a table from these two that would show spending, number of new customers and cost per customer grouped by date and campaign, and I have this query:

SELECT *, spending/signups AS cost 
FROM 
   (SELECT n.date, n.campaign, 
    (SELECT SUM(m.spend) FROM marketing_campaign AS m WHERE m.date = n.date AND m.campaign = n.campaign) AS spending, 
    COUNT(n.customer_id) AS signups 
FROM new_customers AS n 
GROUP BY n.date, n.campaign) AS tab1;

This query seems to be working correctly but now I need to group by months as well. I've tried doing:

SELECT *, spending/signups AS cost 
FROM 
   (SELECT month(n.date), n.campaign, 
    (SELECT SUM(m.spend) FROM marketing_campaign AS m WHERE m.date = n.date AND m.campaign = n.campaign) AS spending, 
    COUNT(n.customer_id) AS signups 
FROM new_customers AS n 
GROUP BY month(n.date), n.campaign) AS tab1;

And:

SELECT *, spending/signups AS cost 
FROM 
   (SELECT extract(month, n.date), n.campaign, 
    (SELECT SUM(m.spend) FROM marketing_campaign AS m WHERE m.date = n.date AND m.campaign = n.campaign) AS spending, 
    COUNT(n.customer_id) AS signups 
FROM new_customers AS n 
GROUP BY extract(month, n.date), n.campaign) AS tab1;

But the both produce error 1055: Expression #3 of SELECT list is not in GROUP BY clause and contains non-aggregated column. 'database_2.n.date' which is not functionally dependent on columns in GROUP BY clause. I'm not too sure how to get the months out of the date otherwise and would appreciate any suggestions!

Becca
  • 1
  • 1
  • Error messages are so much more informative than error numbers – HoneyBadger Aug 18 '22 at 16:25
  • Sorry, the message read like this: Error Code: 1055. Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database_2.n.date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by – Becca Aug 18 '22 at 16:26
  • Don't use a correlated subquery. Join with the `marketing_campaign` table and then aggregate, or join with a subquery that's aggregated and grouped. – Barmar Aug 18 '22 at 16:29

0 Answers0