Hey.
What's going on, everybody?
This is in your course teaching assistant.
And you've just finished this last lecture here about group bye and the joys of learning group bias
called says.
And you're probably a little bit confused.
It was a little bit wonky.
Kolt talked about how when you do this particular query select title author l name from books grouped
by author L name, you get some really bizarre results where you have the titles, but it doesn't show
all the titles.
It just kind of randomly picks a title.
And then you have your unique author L names on the right hand side like you would expect.
But why isn't it showing the rest of the titles?
So the remainder of this lecture, he explains what's going on there.
But there is something going on above and beyond that, and that's what we're going to talk about in
this quick lecture.
So students, including yourself, have encountered an error.
So that's what this is a Q&A thread where a student is reporting this error.
And that is that when you run this query, the select title author l name from books grouped by author
name, you get this error in the terminal, right?
It doesn't even work.
This is expression number one of select lists is not in the group by clause and it contains not a non
aggregated column.
And that's the bookstore title column which is not functionally dependent on columns in the group by
clause.
This is incompatible with the SQL mode equals only full group by.
And so our other teaching assistant Bob has a wonderful explanation here.
If you want to read into it to see exactly what's going on here, I highly encourage that.
He even offers up some alternate solutions that you can go through and all the output that you can expect
from this.
But at face value, what's happening here is that Colt was using an older version of my SQL when he
recorded these lectures, some version like 5.5 or something like that.
And so in the newer versions, like the ones that we're using now, they have it now to where the SQL
mode only for Group B is enabled by default.
So that's the difference.
Back when Colt recorded the video, only for group by was not enabled by default.
And now in the newer versions like the one that you're using with Gorme, it is enabled and all that
means is that things like this wonky output of the titles that are missing titles in this group by you're
not going to get that.
It's not even going to let you get that far.
So you're not going to get that confusing output because it's going to nip it in the bud and stop it
and say, hey, this isn't how this is supposed to be used grouped by is supposed to basically be used
with different aggregate functions.
Like it goes on to show you the count function where you can see how many titles are given unique authors.
So that's the sort of behavior that you're supposed to get grouped by.
You're not supposed to be able to use it just with a random column like the title.
And so instead of just giving you some confusing output, it stops it and it gives you an error like
the one that you may have already experienced.
So this video is just to let you know that we're aware of the error it was allowed in previous version
of my SQL.
You can change it to where you could run this entire query and get the same output is called.
If you scroll down, you can see Bob has outlined some solutions here for how you can do that.
I personally don't encourage that because in the newer versions they're doing it.
Not that the old way was the wrong way, but they're doing it the better way now, and that is to just
error it out.
So one quick solution is just to remove the title column from this query.
If you want to get it to run and show you the unique authors that have been grouped together so you
can just do a select author l name from books grouped by author l name and it'll show you this column
right here and it'll get rid of the weird results from the title.
So hopefully that helps you understand what's going on here.
Again, the SQL mode only full group buy is now enabled by default in the newer versions of my SQL,
like the one that you're currently using.
And back when Colt recorded this video, it was not enabled and that's why he was able to get this kind
of output.
So you can mimic that.
You can disable the only full group by.
Again, scroll down and read those instructions there if you want to experiment with that.
But the easier solution is just to know that this wasn't supposed to happen to begin with.
And if you want to get around it, you just remove that title column from your query.
Thanks a lot for watching this video and we will catch you in the next one.