WEBVTT 1 00:00:00.210 --> 00:00:01.580 Hi guys and welcome back. 2 00:00:01.580 --> 00:00:03.510 In this video I wanted to tell you about 3 00:00:03.510 --> 00:00:06.330 GROUP BY and how we're gonna use it in our app 4 00:00:06.330 --> 00:00:08.543 to calculate vote percentages. 5 00:00:09.700 --> 00:00:10.970 We saw aggregate functions 6 00:00:10.970 --> 00:00:12.490 when we looked at building functions, 7 00:00:12.490 --> 00:00:14.190 but we did so quite quickly. 8 00:00:14.190 --> 00:00:15.940 But essentially, aggregate functions are used 9 00:00:15.940 --> 00:00:19.110 to retrieve values from grouped columns. 10 00:00:19.110 --> 00:00:21.050 For example, count. 11 00:00:21.050 --> 00:00:24.120 If we use GROUP BY, as we will do in this lecture, 12 00:00:24.120 --> 00:00:26.660 we often want to use an aggregate function 13 00:00:26.660 --> 00:00:29.640 in addition to that, to analyse the grouped data. 14 00:00:29.640 --> 00:00:31.970 I'm going to give you a sample table, 15 00:00:31.970 --> 00:00:33.520 not particularly meaningful data, 16 00:00:33.520 --> 00:00:35.830 but just as an example for us to look at 17 00:00:35.830 --> 00:00:37.080 what GROUP BY does. 18 00:00:37.080 --> 00:00:40.800 Here we've got some data, usernames, vote IDs and poll IDs, 19 00:00:40.800 --> 00:00:42.430 you can think of this data however you want, 20 00:00:42.430 --> 00:00:44.080 it's essentially just an example. 21 00:00:45.090 --> 00:00:47.100 And then, let's bring it over here. 22 00:00:47.100 --> 00:00:50.080 And say we want to get 23 00:00:50.080 --> 00:00:54.620 how many votes a user cast. 24 00:00:54.620 --> 00:00:58.100 You might be tempted to do something like this initially. 25 00:00:58.100 --> 00:01:01.130 You know, select username and account, 26 00:01:01.130 --> 00:01:03.280 of everything from votes 27 00:01:03.280 --> 00:01:05.080 maybe that'll give you what you want. 28 00:01:05.080 --> 00:01:08.260 But really, what this means in SQL is 29 00:01:08.260 --> 00:01:10.190 select the username and account 30 00:01:10.190 --> 00:01:12.350 of everything in the result set, 31 00:01:12.350 --> 00:01:15.130 which is the votes table, from the votes table. 32 00:01:15.130 --> 00:01:17.703 So really what this means is something like that. 33 00:01:18.620 --> 00:01:22.610 You would get the username, and the count of everything, 34 00:01:22.610 --> 00:01:23.530 for each row. 35 00:01:23.530 --> 00:01:24.890 Obviously, that doesn't make sense, 36 00:01:24.890 --> 00:01:27.060 so that would give you an error because 37 00:01:27.060 --> 00:01:29.270 PostgreSQL is gonna realise that this is probably 38 00:01:29.270 --> 00:01:31.230 not what you wan to do. 39 00:01:31.230 --> 00:01:33.882 So, here is what you would wanna do. 40 00:01:33.882 --> 00:01:38.170 You want to select the username from the votes table 41 00:01:38.170 --> 00:01:40.287 and you want to GROUP BY username. 42 00:01:40.287 --> 00:01:43.820 And what that's gonna give you is essentially 43 00:01:43.820 --> 00:01:45.900 one row per username. 44 00:01:45.900 --> 00:01:49.067 It has grabbed all of the rows that have the same username 45 00:01:49.067 --> 00:01:53.070 and has grouped them into one row. 46 00:01:53.070 --> 00:01:55.420 Obviously the question is, "What happens 47 00:01:55.420 --> 00:01:56.730 to the rest of the data?" 48 00:01:56.730 --> 00:01:57.910 Right? 49 00:01:57.910 --> 00:02:00.910 Well, let's take a look at another query here. 50 00:02:00.910 --> 00:02:04.880 Select username and the count of usernames 51 00:02:04.880 --> 00:02:08.050 from the votes table, grouping by username. 52 00:02:08.050 --> 00:02:10.880 Now, this gives you what you want. 53 00:02:10.880 --> 00:02:13.600 Why? Because here we're using an aggregate function, 54 00:02:13.600 --> 00:02:17.590 which is the count, on grouped data. 55 00:02:17.590 --> 00:02:20.430 And when you do that, it is going to affect 56 00:02:20.430 --> 00:02:23.490 the result set as it has been grouped. 57 00:02:23.490 --> 00:02:26.810 Here's another example, moving the table away a bit 58 00:02:26.810 --> 00:02:28.950 because it's gonna take a bit more space. 59 00:02:28.950 --> 00:02:31.740 We've got select the username, the count of usernames, 60 00:02:31.740 --> 00:02:33.770 and the average of the votes 61 00:02:33.770 --> 00:02:35.950 from votes group by username. 62 00:02:35.950 --> 00:02:38.940 Again, the aggregate functions are operating 63 00:02:38.940 --> 00:02:42.160 on the grouped data per group. 64 00:02:42.160 --> 00:02:45.110 So, we're getting the count of usernames 65 00:02:45.110 --> 00:02:48.110 for each username, and the average of the votes 66 00:02:48.110 --> 00:02:49.730 for each username. 67 00:02:49.730 --> 00:02:51.410 And the end result is something like this. 68 00:02:51.410 --> 00:02:53.220 Obviously, it's just an example, 69 00:02:53.220 --> 00:02:55.350 calculating the average of a foreign key, 70 00:02:55.350 --> 00:02:57.230 it's not terribly useful, 71 00:02:57.230 --> 00:02:59.380 but here you can see that the AVG function, 72 00:02:59.380 --> 00:03:00.990 which is another aggregate function, 73 00:03:00.990 --> 00:03:04.200 is operating on the grouped data. 74 00:03:04.200 --> 00:03:06.510 So, just a quick recap before we move on, 75 00:03:06.510 --> 00:03:10.670 when you do GROUP BY you lose access to the other columns 76 00:03:10.670 --> 00:03:12.850 that you're not grouping by. 77 00:03:12.850 --> 00:03:15.250 Then you have access to the column you grouped on 78 00:03:15.250 --> 00:03:17.550 but reduced to unique data points 79 00:03:17.550 --> 00:03:20.100 and you have access to using aggregate functions 80 00:03:20.100 --> 00:03:22.740 on every other column in each group. 81 00:03:22.740 --> 00:03:24.680 Let's go and do this in our app. 82 00:03:24.680 --> 00:03:25.950 We're gonna use this knowledge 83 00:03:25.950 --> 00:03:29.260 to get the vote percentages for each poll. 84 00:03:29.260 --> 00:03:30.740 I'll see you there. 85 00:03:30.740 --> 00:03:32.320 All right, so I'm here in ElephantSQL 86 00:03:32.320 --> 00:03:35.000 and I'm gonna start by showing you the data I've got 87 00:03:35.000 --> 00:03:36.530 in my database. 88 00:03:36.530 --> 00:03:38.850 So, here I've got the polls table, 89 00:03:38.850 --> 00:03:41.840 and you can see we've got one poll. 90 00:03:41.840 --> 00:03:44.200 That poll has three options, 91 00:03:44.200 --> 00:03:45.980 and I'm gonna show them to you right now. 92 00:03:45.980 --> 00:03:47.640 That's Flask, Django, and It depends, 93 00:03:47.640 --> 00:03:50.760 three different IDs there with the same poll ID. 94 00:03:50.760 --> 00:03:53.840 And I've also gonna have and included a few votes in here. 95 00:03:53.840 --> 00:03:54.910 I've done all this, by the way, 96 00:03:54.910 --> 00:03:57.260 just running the Python app we've already built. 97 00:03:57.260 --> 00:03:59.730 And so here we've got six different people 98 00:03:59.730 --> 00:04:01.750 voting on various different options. 99 00:04:01.750 --> 00:04:04.470 You can see that three of them have voted for option one, 100 00:04:04.470 --> 00:04:05.830 two of them voted for option two, 101 00:04:05.830 --> 00:04:08.230 and one of them voted for option three. 102 00:04:08.230 --> 00:04:11.770 So, what were gonna try to do in this part of the video 103 00:04:11.770 --> 00:04:15.260 is find out what percentage of the total votes 104 00:04:15.260 --> 00:04:18.250 are for option one, for option two, and for option three. 105 00:04:18.250 --> 00:04:21.630 So, option one has 50% of the votes, three out of six, 106 00:04:21.630 --> 00:04:23.990 option two has two out of six, 107 00:04:23.990 --> 00:04:27.070 and option three has one out of six. 108 00:04:27.070 --> 00:04:29.170 So how are we gonna do that? 109 00:04:29.170 --> 00:04:34.170 Well, first of all, let's go ahead and find the vote data 110 00:04:34.850 --> 00:04:36.750 and the option that it's for. 111 00:04:36.750 --> 00:04:39.530 So what we're gonna do is select and then were gonna do 112 00:04:39.530 --> 00:04:44.230 options.*, and votes.*, FROM options, 113 00:04:44.230 --> 00:04:45.387 you could just do star by the way, 114 00:04:45.387 --> 00:04:47.860 but this is gonna come in handy afterwards. 115 00:04:47.860 --> 00:04:51.100 And we're gonna LEFT JOIN on votes. 116 00:04:51.100 --> 00:04:54.327 ON options.id = votes.option_id. 117 00:04:55.905 --> 00:04:57.727 This gives us all of the options and votes 118 00:04:57.727 --> 00:04:58.870 for each vote. 119 00:04:58.870 --> 00:05:02.750 So here we've got option one with this vote here, 120 00:05:02.750 --> 00:05:04.250 option two with this vote here, 121 00:05:04.250 --> 00:05:05.520 option three with this vote here, 122 00:05:05.520 --> 00:05:07.370 and then option one again with this vote, 123 00:05:07.370 --> 00:05:08.850 option one again with this vote, 124 00:05:08.850 --> 00:05:11.292 and option two with this vote. 125 00:05:11.292 --> 00:05:13.510 So as you can see, we've got some duplicate data 126 00:05:13.510 --> 00:05:15.070 in terms of the options 127 00:05:15.070 --> 00:05:18.470 because we wanted to accommodate for all the votes. 128 00:05:18.470 --> 00:05:22.220 So the next thing to do is gonna be to GROUP BY. 129 00:05:22.220 --> 00:05:25.890 So I'm gonna do GROUP BY options.id, 130 00:05:25.890 --> 00:05:28.160 and I'm also here gonna add a WHERE clause 131 00:05:28.160 --> 00:05:29.380 right before the GROUP BY, 132 00:05:29.380 --> 00:05:30.773 so we're gonna do WHERE. 133 00:05:31.860 --> 00:05:34.830 And we're gonna do options.pollid = one, 134 00:05:34.830 --> 00:05:37.620 just in case later on we wanna add more polls, 135 00:05:37.620 --> 00:05:39.850 we know that this is something we're gonna have to pass 136 00:05:39.850 --> 00:05:43.930 in our application to filter out any unwanted polls. 137 00:05:43.930 --> 00:05:47.130 At this point, I'm also going to simplify this 138 00:05:47.130 --> 00:05:51.520 a little bit, just so you guys can read it a bit more easily 139 00:05:51.520 --> 00:05:52.353 just like that. 140 00:05:52.353 --> 00:05:54.660 So we've got SELECT, options*, votes*, 141 00:05:54.660 --> 00:05:58.110 FROM options, LEFT JOIN votes, WHERE the poll ID is one, 142 00:05:58.110 --> 00:06:00.401 and we're grouping by option_id. 143 00:06:00.401 --> 00:06:02.570 So if we press run right now, we're gonna get an error 144 00:06:02.570 --> 00:06:05.100 because we're selecting all of the columns here. 145 00:06:05.100 --> 00:06:09.850 We're gonna do options.id, options.option_text, 146 00:06:09.850 --> 00:06:11.460 because we're gonna group by option_id, 147 00:06:11.460 --> 00:06:15.660 these two are still sort of one value per row, 148 00:06:15.660 --> 00:06:17.320 so we can still select them. 149 00:06:17.320 --> 00:06:22.003 And then we're gonna do the COUNT of votes.option_id. 150 00:06:22.940 --> 00:06:26.360 Because the votes.option_id is gonna get grouped 151 00:06:26.360 --> 00:06:29.780 when we group by ID here in the options table, 152 00:06:29.780 --> 00:06:33.850 we're gonna end up with the count for each row. 153 00:06:33.850 --> 00:06:38.090 So when we run this, you'll see that we get three votes 154 00:06:38.090 --> 00:06:40.300 for option one, two votes for option two, 155 00:06:40.300 --> 00:06:41.790 and one vote for option three. 156 00:06:41.790 --> 00:06:45.090 Now, it's convention when you're using an aggregate function 157 00:06:45.090 --> 00:06:46.750 that you give it an alias. 158 00:06:46.750 --> 00:06:49.910 So I'm gonna do vote COUNT there. 159 00:06:49.910 --> 00:06:52.840 So you can simply do that, and when you run this query 160 00:06:52.840 --> 00:06:56.493 you're gonna get vote count as the header for this column. 161 00:06:57.340 --> 00:06:59.290 Now, the next thing that we wanna do though 162 00:06:59.290 --> 00:07:03.565 is find out this number three, how much it is 163 00:07:03.565 --> 00:07:07.420 as a percentage of the total number of votes. 164 00:07:07.420 --> 00:07:10.740 So three divided by six, right? 165 00:07:10.740 --> 00:07:13.030 This is where it starts to get a bit more complicated. 166 00:07:13.030 --> 00:07:14.800 Because in normal mathematics, what you would do 167 00:07:14.800 --> 00:07:18.310 is you would say something like COUNT of votes.option_id, 168 00:07:18.310 --> 00:07:23.310 that gives you three, divided by the SUM of the COUNT 169 00:07:24.700 --> 00:07:27.470 of votes.option_id multiplied by 100 170 00:07:27.470 --> 00:07:29.190 to get a proper percentage. 171 00:07:29.190 --> 00:07:32.670 So if we were to do this, we would get, for example, 172 00:07:32.670 --> 00:07:37.620 for this first row three, divided by six presumably, 173 00:07:37.620 --> 00:07:41.600 which gives us 0.5, times one hundred, 50. 174 00:07:41.600 --> 00:07:43.360 And that would be the 50%, 175 00:07:43.360 --> 00:07:45.000 and you could potentially even do something like 176 00:07:45.000 --> 00:07:47.160 AS percentage here, if you wanted. 177 00:07:47.160 --> 00:07:49.530 And then in theory this should give you 178 00:07:49.530 --> 00:07:50.960 the numbers you want. 179 00:07:50.960 --> 00:07:54.070 But, it's not so easy. 180 00:07:54.070 --> 00:07:58.270 Sadly enough, aggregate function calls cannot be nested. 181 00:07:58.270 --> 00:08:01.050 That means that you can't put the COUNT of something 182 00:08:01.050 --> 00:08:03.260 inside the SUM. 183 00:08:03.260 --> 00:08:05.187 So, the keener eyed among you may say, 184 00:08:05.187 --> 00:08:06.760 "Okay well just use the vote counter 185 00:08:06.760 --> 00:08:08.180 you defined earlier, right?" 186 00:08:08.180 --> 00:08:10.570 Sadly, no it doesn't quite work that way, 187 00:08:10.570 --> 00:08:12.299 this column here is evaluated 188 00:08:12.299 --> 00:08:15.050 after this whole thing has ran essentially, 189 00:08:15.050 --> 00:08:18.740 it's an alias for this and it's not available 190 00:08:18.740 --> 00:08:20.550 at this point in time. 191 00:08:20.550 --> 00:08:22.050 So you can't do that. 192 00:08:22.050 --> 00:08:22.883 So what do we do? 193 00:08:22.883 --> 00:08:24.800 Because clearly the error remains, 194 00:08:24.800 --> 00:08:27.362 aggregate functions called cannot be nested. 195 00:08:27.362 --> 00:08:30.090 Well, what we wanna do is 196 00:08:30.090 --> 00:08:35.090 we want to grab the count of the votes for this option 197 00:08:35.210 --> 00:08:37.270 that we are currently working on here, 198 00:08:37.270 --> 00:08:39.200 after we've grouped. 199 00:08:39.200 --> 00:08:42.910 But, we want to grab the SUM of everything, 200 00:08:42.910 --> 00:08:46.500 not just things after grouping. 201 00:08:46.500 --> 00:08:50.340 And so, what we're gonna do is we're gonna do OVER. 202 00:08:50.340 --> 00:08:52.030 And then the two brackets. 203 00:08:52.030 --> 00:08:55.980 Now, I'll quickly explain how this works 204 00:08:55.980 --> 00:08:57.290 but in the next video we're gonna have 205 00:08:57.290 --> 00:08:59.010 a more in-depth explanation. 206 00:08:59.010 --> 00:09:00.720 Essentially what's happening here 207 00:09:00.720 --> 00:09:04.630 is we're saying, "Grab me the counts of the option_id 208 00:09:04.630 --> 00:09:08.030 for this group that we're currently working on, 209 00:09:08.030 --> 00:09:13.030 divided by the SUM of everything, 210 00:09:13.230 --> 00:09:14.530 that's what OVER does. 211 00:09:14.530 --> 00:09:18.350 So it ignores the groupings, and then 212 00:09:18.350 --> 00:09:19.860 the thing that we're summing 213 00:09:19.860 --> 00:09:22.870 is the count of the things that are in the groups. 214 00:09:22.870 --> 00:09:24.960 So I know this is all a bit complicated, 215 00:09:24.960 --> 00:09:26.860 but again, we're gonna explain exactly 216 00:09:26.860 --> 00:09:28.540 what is going on with this thing here 217 00:09:28.540 --> 00:09:30.730 in the next video, it is a little bit trickier. 218 00:09:30.730 --> 00:09:32.830 This is called a window function, by the way, 219 00:09:32.830 --> 00:09:35.553 and essentially what it does, is it allows us to run 220 00:09:35.553 --> 00:09:38.197 something, in this case it's the SUM, 221 00:09:38.197 --> 00:09:40.600 but not the COUNT just the SUM, 222 00:09:40.600 --> 00:09:43.980 over a different data set than the ones 223 00:09:43.980 --> 00:09:47.030 that we are working with in the rest of the query. 224 00:09:47.030 --> 00:09:49.430 And so we are running the SUM in a different data set 225 00:09:49.430 --> 00:09:51.640 and that is allowed in this case. 226 00:09:51.640 --> 00:09:54.370 So if we do this, now we will get what we want. 227 00:09:54.370 --> 00:09:58.330 So we get 50%, 33, and 0.16. 228 00:09:58.330 --> 00:10:00.730 Okay, I'm just gonna rename this alias there 229 00:10:00.730 --> 00:10:04.060 to Vote Percentage to match what we've got in our notes. 230 00:10:04.060 --> 00:10:05.660 So now that we've got all of this, 231 00:10:05.660 --> 00:10:07.880 we can grab the whole thing, 232 00:10:07.880 --> 00:10:10.260 and we're gonna go over to PyCharm 233 00:10:10.260 --> 00:10:13.800 and use this in our code, so let's do that. 234 00:10:13.800 --> 00:10:16.960 So I'm here in PyCharm, I'm gonna put that whole query 235 00:10:16.960 --> 00:10:21.270 into its own variable, SELECT_POLL_VOTE_DETAILS, 236 00:10:21.270 --> 00:10:24.780 and that's gonna be a multi lined query like that. 237 00:10:24.780 --> 00:10:26.710 Where it's gonna grab everything 238 00:10:26.710 --> 00:10:29.090 and that we were getting in ElephantSQL, 239 00:10:29.090 --> 00:10:31.060 so that's the option_id and the text, 240 00:10:31.060 --> 00:10:33.040 the count of votes for that option, 241 00:10:33.040 --> 00:10:35.020 as well as the percentage. 242 00:10:35.020 --> 00:10:38.060 Now we can go down to get poll and vote results 243 00:10:38.060 --> 00:10:39.450 and we're gonna make use of this. 244 00:10:39.450 --> 00:10:43.030 So what we'll do is cursor.execute. 245 00:10:43.030 --> 00:10:45.890 SELECT poll and vote details there, 246 00:10:45.890 --> 00:10:48.400 make sure to pass the poll ID that we get 247 00:10:48.400 --> 00:10:51.110 as an argument, and I'm gonna go up to the top actually 248 00:10:51.110 --> 00:10:53.350 and make sure that instead of hardcoding the one here 249 00:10:53.350 --> 00:10:55.970 in the WHERE clause, we're doing percent s, 250 00:10:55.970 --> 00:10:58.420 so that we can pass in the value. 251 00:10:58.420 --> 00:11:00.500 And then in here, we'll go back 252 00:11:00.500 --> 00:11:02.643 and say return cursor.fetchal. 253 00:11:03.600 --> 00:11:05.930 Once again remember this is multiple rows being returned, 254 00:11:05.930 --> 00:11:08.650 one per option, so that's why we need fetchal. 255 00:11:08.650 --> 00:11:10.870 Let's go over to app.py now, 256 00:11:10.870 --> 00:11:13.220 and see where this is used. 257 00:11:13.220 --> 00:11:16.940 So we've got database.get_poll_and_vote_results in app.py 258 00:11:17.841 --> 00:11:20.930 and then we're iterating over the response 259 00:11:20.930 --> 00:11:23.260 which gives us the _id, the option_text, the count, 260 00:11:23.260 --> 00:11:25.690 and the percentage and we're just printing them out. 261 00:11:25.690 --> 00:11:29.210 So let's run this and let's see what happens. 262 00:11:29.210 --> 00:11:32.530 So, I'm connected to the database that we saw in ElephantSQL 263 00:11:32.530 --> 00:11:35.337 I'm gonna type four, poll is ID one, 264 00:11:35.337 --> 00:11:37.710 and we get back this. 265 00:11:37.710 --> 00:11:39.240 Which is exactly what we wanted. 266 00:11:39.240 --> 00:11:41.270 Flask got three votes, 50% of total, 267 00:11:41.270 --> 00:11:43.830 Django got two votes, 33.3% of total, 268 00:11:43.830 --> 00:11:46.330 and It depends got one votes. 269 00:11:46.330 --> 00:11:49.130 This, we could do better, we could say one vote instead. 270 00:11:49.130 --> 00:11:51.760 I'll let you figure that out if you want to improve 271 00:11:51.760 --> 00:11:55.350 this string a bit further, but for me this is okay. 272 00:11:55.350 --> 00:11:58.150 All right, so that's everything for this video, 273 00:11:58.150 --> 00:12:01.320 just wanted to show you how you can use the GROUP BY 274 00:12:01.320 --> 00:12:03.180 in order to get the vote percentages, 275 00:12:03.180 --> 00:12:06.390 and we've also surreptitiously added there 276 00:12:06.390 --> 00:12:08.020 the WINDOW function that we're gonna look at 277 00:12:08.020 --> 00:12:09.210 in the next video. 278 00:12:09.210 --> 00:12:10.900 All right, thank you guys for joining me, 279 00:12:10.900 --> 00:12:12.180 thanks for watching this video. 280 00:12:12.180 --> 00:12:13.887 I'll see you in the next one.