Resolved: error 1055 im mysql project
#C Applying the MIN aggregate function in the previous step requires grouping the results
SELECT
student_info.student_id,date_registered,
MIN(date_watched) AS first_date_watched,
MIN(date_purchased) AS first_date_purchased,
DATEDIFF(date_watched,date_registered) AS days_diff_reg_watch,
DATEDIFF(date_watched,date_purchased) AS days_diff_watch_purch
FROM
student_engagement
JOIN
student_info ON student_info.student_id = student_engagement.student_id
JOIN
student_purchases ON student_info.student_id = student_engagement.student_id
GROUP BY student_info.student_id, date_registered;
; exp
when i run it, i get this response:
Expression #5 of select list is not in groupby clause, contains non aggregated column which is not functionally dpendent on group column.
i have googled this error to fix it BUT if i were to indlcue ONLY FULL GROUP BY clause, i percevie it will give an ambigous result, kindly advise on neccary steps to take.
thank you
Thanks for reaching out.
To fix this error, you need to adjust the GROUP BY clause to include all columns that aren’t in aggregate functions (MIN(), etc.). Here’s the step-by-step solution:
1) Identify Non-Aggregated Columns: In your query, date_registered is grouped, but date_watched and date_purchased are not aggregated and aren’t in GROUP BY, so MySQL throws an error.
Modify the query so only the aggregated columns - MIN(date_watched), MIN(date_purchased) are selected, and calculate days_diff_reg_watch and days_diff_watch_purch after the GROUP BY clause.
Here’s a modified version of your query:
SELECT
student_id,
date_registered,
first_date_watched,
first_date_purchased,
DATEDIFF(first_date_watched, date_registered) AS days_diff_reg_watch,
DATEDIFF(first_date_watched, first_date_purchased) AS days_diff_watch_purch
FROM (
SELECT
student_info.student_id,
date_registered,
MIN(date_watched) AS first_date_watched,
MIN(date_purchased) AS first_date_purchased
FROM
student_engagement
JOIN student_info ON student_info.student_id = student_engagement.student_id
JOIN student_purchases ON student_info.student_id = student_engagement.student_id
GROUP BY student_info.student_id, date_registered
) AS s;
Hope this helps.
Best,
Tsvetelin
i ran this query, it showed error code,
normally whne an aggregated function is used in a SELECT statement, it has include a GROUP BY of all the non aggregated column( this will group rows of same values into a summary right)...it is supposed to be GROUP BY student_info.student_id, date_registered
this i tested as inner query but it is an error it giving me, pls other suggestion is highly welcomed
tx
select @@global.sql_mode;
set @@global.sql_mode := concat('ONLY_FULL_GROUP_BY,', @@global.sql_mode);
but i see error code 2013. saying it taking to lon time to run hence lost connection to mysql. server. my. internet is fast. kindly advise thank you
Hi abiola and Tsvetelin!
I hope you don't mind if I join the conversation.
@abiola: Thank you very much for providing detailed explanations of the issues you are facing.
Indeed, I would have suggested changing the global SQL mode, but you mentioned already that it didn't work.
May I please ask - which version of SQL are you using? This command my not run on versions earlier than 8.0.
If you are using 8.0 or after, you could technically increase the keep-alive and/or wait time out. This should help.
Please report if these suggestions don't help and we'll take it from there. Thank you.
Hope this helps.
Best,
Martin,
The 365 Team
Your advice did help in resolving it.
thank you.
Great!
Thank you for letting us know.
Good luck with the rest of the course and please feel free to post another question should you need assistance. Thank you.
Kind regards,
Martin