Resolved: Got an error while executing sql

Question

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

Answer 1

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 AS
registration_date,
MAX(p.purchase_date) AS
purchase_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

Answer 2

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

Resolved: Got an error while executing sql

Submit an answer