1
00:00:00,120 --> 00:00:00,390
Hey.

2
00:00:00,390 --> 00:00:01,380
What's going on, everybody?

3
00:00:01,380 --> 00:00:03,840
This is in your course teaching assistant.

4
00:00:03,840 --> 00:00:10,020
And you've just finished this last lecture here about group bye and the joys of learning group bias

5
00:00:10,030 --> 00:00:10,800
called says.

6
00:00:10,800 --> 00:00:13,260
And you're probably a little bit confused.

7
00:00:13,260 --> 00:00:14,820
It was a little bit wonky.

8
00:00:14,820 --> 00:00:20,820
Kolt talked about how when you do this particular query select title author l name from books grouped

9
00:00:20,820 --> 00:00:25,770
by author L name, you get some really bizarre results where you have the titles, but it doesn't show

10
00:00:25,770 --> 00:00:26,420
all the titles.

11
00:00:26,430 --> 00:00:29,040
It just kind of randomly picks a title.

12
00:00:29,040 --> 00:00:33,210
And then you have your unique author L names on the right hand side like you would expect.

13
00:00:33,210 --> 00:00:35,760
But why isn't it showing the rest of the titles?

14
00:00:35,760 --> 00:00:39,150
So the remainder of this lecture, he explains what's going on there.

15
00:00:39,150 --> 00:00:43,650
But there is something going on above and beyond that, and that's what we're going to talk about in

16
00:00:43,650 --> 00:00:44,730
this quick lecture.

17
00:00:44,730 --> 00:00:49,560
So students, including yourself, have encountered an error.

18
00:00:49,650 --> 00:00:53,430
So that's what this is a Q&A thread where a student is reporting this error.

19
00:00:53,430 --> 00:00:59,070
And that is that when you run this query, the select title author l name from books grouped by author

20
00:00:59,190 --> 00:01:01,790
name, you get this error in the terminal, right?

21
00:01:01,800 --> 00:01:02,550
It doesn't even work.

22
00:01:02,580 --> 00:01:08,220
This is expression number one of select lists is not in the group by clause and it contains not a non

23
00:01:08,220 --> 00:01:09,450
aggregated column.

24
00:01:09,450 --> 00:01:14,010
And that's the bookstore title column which is not functionally dependent on columns in the group by

25
00:01:14,010 --> 00:01:14,610
clause.

26
00:01:14,610 --> 00:01:18,390
This is incompatible with the SQL mode equals only full group by.

27
00:01:18,660 --> 00:01:23,100
And so our other teaching assistant Bob has a wonderful explanation here.

28
00:01:23,100 --> 00:01:27,600
If you want to read into it to see exactly what's going on here, I highly encourage that.

29
00:01:27,600 --> 00:01:32,370
He even offers up some alternate solutions that you can go through and all the output that you can expect

30
00:01:32,370 --> 00:01:33,150
from this.

31
00:01:33,150 --> 00:01:39,240
But at face value, what's happening here is that Colt was using an older version of my SQL when he

32
00:01:39,240 --> 00:01:43,320
recorded these lectures, some version like 5.5 or something like that.

33
00:01:43,320 --> 00:01:49,200
And so in the newer versions, like the ones that we're using now, they have it now to where the SQL

34
00:01:49,200 --> 00:01:52,590
mode only for Group B is enabled by default.

35
00:01:52,590 --> 00:01:53,520
So that's the difference.

36
00:01:53,520 --> 00:01:58,110
Back when Colt recorded the video, only for group by was not enabled by default.

37
00:01:58,140 --> 00:02:02,700
And now in the newer versions like the one that you're using with Gorme, it is enabled and all that

38
00:02:02,700 --> 00:02:10,860
means is that things like this wonky output of the titles that are missing titles in this group by you're

39
00:02:10,860 --> 00:02:11,610
not going to get that.

40
00:02:11,610 --> 00:02:13,320
It's not even going to let you get that far.

41
00:02:13,320 --> 00:02:17,460
So you're not going to get that confusing output because it's going to nip it in the bud and stop it

42
00:02:17,460 --> 00:02:24,540
and say, hey, this isn't how this is supposed to be used grouped by is supposed to basically be used

43
00:02:24,540 --> 00:02:26,280
with different aggregate functions.

44
00:02:26,280 --> 00:02:34,260
Like it goes on to show you the count function where you can see how many titles are given unique authors.

45
00:02:34,260 --> 00:02:36,870
So that's the sort of behavior that you're supposed to get grouped by.

46
00:02:36,870 --> 00:02:41,490
You're not supposed to be able to use it just with a random column like the title.

47
00:02:41,640 --> 00:02:47,550
And so instead of just giving you some confusing output, it stops it and it gives you an error like

48
00:02:47,550 --> 00:02:49,410
the one that you may have already experienced.

49
00:02:49,470 --> 00:02:56,280
So this video is just to let you know that we're aware of the error it was allowed in previous version

50
00:02:56,280 --> 00:02:57,060
of my SQL.

51
00:02:57,090 --> 00:03:02,880
You can change it to where you could run this entire query and get the same output is called.

52
00:03:02,910 --> 00:03:09,360
If you scroll down, you can see Bob has outlined some solutions here for how you can do that.

53
00:03:09,360 --> 00:03:13,530
I personally don't encourage that because in the newer versions they're doing it.

54
00:03:13,980 --> 00:03:18,510
Not that the old way was the wrong way, but they're doing it the better way now, and that is to just

55
00:03:18,510 --> 00:03:19,380
error it out.

56
00:03:19,380 --> 00:03:26,190
So one quick solution is just to remove the title column from this query.

57
00:03:26,190 --> 00:03:32,010
If you want to get it to run and show you the unique authors that have been grouped together so you

58
00:03:32,010 --> 00:03:37,500
can just do a select author l name from books grouped by author l name and it'll show you this column

59
00:03:37,500 --> 00:03:40,920
right here and it'll get rid of the weird results from the title.

60
00:03:41,010 --> 00:03:44,220
So hopefully that helps you understand what's going on here.

61
00:03:44,220 --> 00:03:51,600
Again, the SQL mode only full group buy is now enabled by default in the newer versions of my SQL,

62
00:03:51,600 --> 00:03:53,160
like the one that you're currently using.

63
00:03:53,160 --> 00:03:57,960
And back when Colt recorded this video, it was not enabled and that's why he was able to get this kind

64
00:03:57,960 --> 00:03:58,740
of output.

65
00:03:58,740 --> 00:04:00,810
So you can mimic that.

66
00:04:00,810 --> 00:04:03,030
You can disable the only full group by.

67
00:04:03,420 --> 00:04:07,170
Again, scroll down and read those instructions there if you want to experiment with that.

68
00:04:07,170 --> 00:04:11,580
But the easier solution is just to know that this wasn't supposed to happen to begin with.

69
00:04:11,580 --> 00:04:15,870
And if you want to get around it, you just remove that title column from your query.

70
00:04:15,900 --> 00:04:19,200
Thanks a lot for watching this video and we will catch you in the next one.