Jose: Hi guys and welcome back.
In this video I wanted to tell you about
GROUP BY and how we're gonna use it in our app
to calculate vote percentages.
We saw aggregate functions
when we looked at building functions,
but we did so quite quickly.
But essentially, aggregate functions are used
to retrieve values from grouped columns.
For example, count.
If we use GROUP BY, as we will do in this lecture,
we often want to use an aggregate function
in addition to that, to analyse the grouped data.
I'm going to give you a sample table,
not particularly meaningful data,
but just as an example for us to look at
what GROUP BY does.
Here we've got some data, usernames, vote IDs and poll IDs,
you can think of this data however you want,
it's essentially just an example.
And then, let's bring it over here.
And say we want to get
how many votes a user cast.
You might be tempted to do something like this initially.
You know, select username and account,
of everything from votes
maybe that'll give you what you want.
But really, what this means in SQL is
select the username and account
of everything in the result set,
which is the votes table, from the votes table.
So really what this means is something like that.
You would get the username, and the count of everything,
for each row.
Obviously, that doesn't make sense,
so that would give you an error because
PostgreSQL is gonna realise that this is probably
not what you wan to do.
So, here is what you would wanna do.
You want to select the username from the votes table
and you want to GROUP BY username.
And what that's gonna give you is essentially
one row per username.
It has grabbed all of the rows that have the same username
and has grouped them into one row.
Obviously the question is, "What happens
to the rest of the data?"
Right?
Well, let's take a look at another query here.
Select username and the count of usernames
from the votes table, grouping by username.
Now, this gives you what you want.
Why? Because here we're using an aggregate function,
which is the count, on grouped data.
And when you do that, it is going to affect
the result set as it has been grouped.
Here's another example, moving the table away a bit
because it's gonna take a bit more space.
We've got select the username, the count of usernames,
and the average of the votes
from votes group by username.
Again, the aggregate functions are operating
on the grouped data per group.
So, we're getting the count of usernames
for each username, and the average of the votes
for each username.
And the end result is something like this.
Obviously, it's just an example,
calculating the average of a foreign key,
it's not terribly useful,
but here you can see that the AVG function,
which is another aggregate function,
is operating on the grouped data.
So, just a quick recap before we move on,
when you do GROUP BY you lose access to the other columns
that you're not grouping by.
Then you have access to the column you grouped on
but reduced to unique data points
and you have access to using aggregate functions
on every other column in each group.
Let's go and do this in our app.
We're gonna use this knowledge
to get the vote percentages for each poll.
I'll see you there.
All right, so I'm here in ElephantSQL
and I'm gonna start by showing you the data I've got
in my database.
So, here I've got the polls table,
and you can see we've got one poll.
That poll has three options,
and I'm gonna show them to you right now.
That's Flask, Django, and It depends,
three different IDs there with the same poll ID.
And I've also gonna have and included a few votes in here.
I've done all this, by the way,
just running the Python app we've already built.
And so here we've got six different people
voting on various different options.
You can see that three of them have voted for option one,
two of them voted for option two,
and one of them voted for option three.
So, what were gonna try to do in this part of the video
is find out what percentage of the total votes
are for option one, for option two, and for option three.
So, option one has 50% of the votes, three out of six,
option two has two out of six,
and option three has one out of six.
So how are we gonna do that?
Well, first of all, let's go ahead and find the vote data
and the option that it's for.
So what we're gonna do is select and then were gonna do
options.*, and votes.*, FROM options,
you could just do star by the way,
but this is gonna come in handy afterwards.
And we're gonna LEFT JOIN on votes.
ON options.id = votes.option_id.
This gives us all of the options and votes
for each vote.
So here we've got option one with this vote here,
option two with this vote here,
option three with this vote here,
and then option one again with this vote,
option one again with this vote,
and option two with this vote.
So as you can see, we've got some duplicate data
in terms of the options
because we wanted to accommodate for all the votes.
So the next thing to do is gonna be to GROUP BY.
So I'm gonna do GROUP BY options.id,
and I'm also here gonna add a WHERE clause
right before the GROUP BY,
so we're gonna do WHERE.
And we're gonna do options.pollid = one,
just in case later on we wanna add more polls,
we know that this is something we're gonna have to pass
in our application to filter out any unwanted polls.
At this point, I'm also going to simplify this
a little bit, just so you guys can read it a bit more easily
just like that.
So we've got SELECT, options*, votes*,
FROM options, LEFT JOIN votes, WHERE the poll ID is one,
and we're grouping by option_id.
So if we press run right now, we're gonna get an error
because we're selecting all of the columns here.
We're gonna do options.id, options.option_text,
because we're gonna group by option_id,
these two are still sort of one value per row,
so we can still select them.
And then we're gonna do the COUNT of votes.option_id.
Because the votes.option_id is gonna get grouped
when we group by ID here in the options table,
we're gonna end up with the count for each row.
So when we run this, you'll see that we get three votes
for option one, two votes for option two,
and one vote for option three.
Now, it's convention when you're using an aggregate function
that you give it an alias.
So I'm gonna do vote COUNT there.
So you can simply do that, and when you run this query
you're gonna get vote count as the header for this column.
Now, the next thing that we wanna do though
is find out this number three, how much it is
as a percentage of the total number of votes.
So three divided by six, right?
This is where it starts to get a bit more complicated.
Because in normal mathematics, what you would do
is you would say something like COUNT of votes.option_id,
that gives you three, divided by the SUM of the COUNT
of votes.option_id multiplied by 100
to get a proper percentage.
So if we were to do this, we would get, for example,
for this first row three, divided by six presumably,
which gives us 0.5, times one hundred, 50.
And that would be the 50%,
and you could potentially even do something like
AS percentage here, if you wanted.
And then in theory this should give you
the numbers you want.
But, it's not so easy.
Sadly enough, aggregate function calls cannot be nested.
That means that you can't put the COUNT of something
inside the SUM.
So, the keener eyed among you may say,
"Okay well just use the vote counter
you defined earlier, right?"
Sadly, no it doesn't quite work that way,
this column here is evaluated
after this whole thing has ran essentially,
it's an alias for this and it's not available
at this point in time.
So you can't do that.
So what do we do?
Because clearly the error remains,
aggregate functions called cannot be nested.
Well, what we wanna do is
we want to grab the count of the votes for this option
that we are currently working on here,
after we've grouped.
But, we want to grab the SUM of everything,
not just things after grouping.
And so, what we're gonna do is we're gonna do OVER.
And then the two brackets.
Now, I'll quickly explain how this works
but in the next video we're gonna have
a more in-depth explanation.
Essentially what's happening here
is we're saying, "Grab me the counts of the option_id
for this group that we're currently working on,
divided by the SUM of everything,
that's what OVER does.
So it ignores the groupings, and then
the thing that we're summing
is the count of the things that are in the groups.
So I know this is all a bit complicated,
but again, we're gonna explain exactly
what is going on with this thing here
in the next video, it is a little bit trickier.
This is called a window function, by the way,
and essentially what it does, is it allows us to run
something, in this case it's the SUM,
but not the COUNT just the SUM,
over a different data set than the ones
that we are working with in the rest of the query.
And so we are running the SUM in a different data set
and that is allowed in this case.
So if we do this, now we will get what we want.
So we get 50%, 33, and 0.16.
Okay, I'm just gonna rename this alias there
to Vote Percentage to match what we've got in our notes.
So now that we've got all of this,
we can grab the whole thing,
and we're gonna go over to PyCharm
and use this in our code, so let's do that.
So I'm here in PyCharm, I'm gonna put that whole query
into its own variable, SELECT_POLL_VOTE_DETAILS,
and that's gonna be a multi lined query like that.
Where it's gonna grab everything
and that we were getting in ElephantSQL,
so that's the option_id and the text,
the count of votes for that option,
as well as the percentage.
Now we can go down to get poll and vote results
and we're gonna make use of this.
So what we'll do is cursor.execute.
SELECT poll and vote details there,
make sure to pass the poll ID that we get
as an argument, and I'm gonna go up to the top actually
and make sure that instead of hardcoding the one here
in the WHERE clause, we're doing percent s,
so that we can pass in the value.
And then in here, we'll go back
and say return cursor.fetchal.
Once again remember this is multiple rows being returned,
one per option, so that's why we need fetchal.
Let's go over to app.py now,
and see where this is used.
So we've got database.get_poll_and_vote_results in app.py
and then we're iterating over the response
which gives us the _id, the option_text, the count,
and the percentage and we're just printing them out.
So let's run this and let's see what happens.
So, I'm connected to the database that we saw in ElephantSQL
I'm gonna type four, poll is ID one,
and we get back this.
Which is exactly what we wanted.
Flask got three votes, 50% of total,
Django got two votes, 33.3% of total,
and It depends got one votes.
This, we could do better, we could say one vote instead.
I'll let you figure that out if you want to improve
this string a bit further, but for me this is okay.
All right, so that's everything for this video,
just wanted to show you how you can use the GROUP BY
in order to get the vote percentages,
and we've also surreptitiously added there
the WINDOW function that we're gonna look at
in the next video.
All right, thank you guys for joining me,
thanks for watching this video.
I'll see you in the next one.