Resolved: Got an error while executing sql
I got this error while trying to execute the sql mention in the session:
Error Code: 1055. Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'signup_flow.p.purchase_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Hi Yash!
Great to have you in this course and thanks for reaching out!
The error you're encountering is due to the ONLY_FULL_GROUP_BY SQL mode in MySQL. This mode enforces specific rules for the GROUP BY clause in SQL queries. It basically requires all selected columns to be either aggregated or functionally dependent on the GROUP BY columns
To prevent this error from occurring, you can apply an aggregation function to the purchase_date column, such as MAX(), and make the query compliant with the GROUP BY clause. For instance, you can modify the first CTE like this:
WITH total_visitors AS ( SELECT v.visitor_id, v.first_visit_date, s.date_registered ASregistration_date, MAX(p.purchase_date) ASpurchase_date FROM visitors AS v LEFT JOIN students AS s ON v.user_id =s.user_id LEFT JOIN student_purchases AS p ON v.user_id= p.user_id GROUP BY visitor_id),
...
Hope this helps.
Best,
Ivan
Please note that you'll probably need to adjust the signup_types_mod query to this mode as well. Here, we can use an aggregate function to bypass aggregation in a way that doesn't alter the intended results. For instance using the ANY_VALUE() function:
SELECT ac.visitor_id, s.user_id, ANY_VALUE(CAST(s.date_registered AS DATE)) AS registration_date, ANY_VALUE(CAST(ac.action_date AS DATE)) AS signup_date, (CASE WHEN ac.action_name LIKE '%google%' THEN 'google' WHEN ac.action_name LIKE '%facebook%' THEN 'facebook' WHEN ac.action_name LIKE '%linkedin%' THEN 'linkedin' ELSE 'email' END) AS signup_method, ANY_VALUE(CASE WHEN
...
Best,
Ivan
Submit an answer
