Next up, we've got a little bit more of a complicated result we're looking for.
It involves each reviewer so their first name and their last name and then the number of reviews that
they've left.
And then the minimum score, the maximum score, the average score.
And then status here should be a conditional that will be either active or inactive depending on whether
a user has left a review or not.
So Marlon Crawford, Crawford, whoever that is, has zero ratings that they've zero reviews, so they're
considered inactive, but everyone else is active.
So there's a lot going on here.
But at the end of the day, it's just some aggregate functions we covered a while ago, some grouping
and a join.
What are we joining?
Well, information about reviews and reviewers.
So go ahead and try it.
I will start right now.
Here's my attempt so we know that we want first name and last name, but I'm just going to start by
doing a join between reviewers and reviews.
So select star from reviewers.
Join reviews and then we'll do on reviewer dot ID is equal to reviews dot reviewer ID.
All right.
So we'll start with an inner join.
And there we go.
We have the overlap between users and reviews.
But remember, we also want to include Marlon Crawford and any other users that don't have reviews.
And at the moment, he's not in here because we did an inner join.
If I did a left join, we'll get every row from reviewers plus any matching information from reviews.
So I'm going to do a left join instead.
Okay.
And now we have Marlon Crawford.
Now we want to do a group by and we were going to group by first and last name combined although you
could in this example data set just do last name but we're going to do a group by first name, comma,
last name.
And then instead of selecting star, let's select first name, last name, and then let's do count.
But I'm going to do count of rating instead of Count Star.
We'll talk about that in a moment.
And what do we see?
Thomas Stoneman five Reviews.
Domingo Cortez ten reviews.
Cold Steel ten reviews.
Marlon Crawford zero reviews.
That's the count, which is correct.
Now, if I instead had done Count of Star, that would not quite be right.
It would be very close.
But this is counting the rows.
And as we know, Marlon Crawford does not have any reviews, but he does have one row.
So the count here would be one.
But I want the count of the actual ratings, not the count of how many times he appears in our joint
table, because here is the joint table.
He has no ratings, no anything related to ratings, but his name is there and there's a row.
He occurs once.
So when I do Count Star, it's included.
But if I do count of rating.
It's not included.
Okay, So that gives us the count.
What do we have next?
We need to get the minimum and maximum.
Okay, so let's do this on separate lines.
First name.
Last name, count.
We'll do the min rating followed by the MAX rating.
Run this query.
Okay, so it looks good.
We see Thomas Donovan's minimum is seven, his max is 9.5.
That is what I had on the slide.
However, for Marlon Crawford, it's not quite correct because we see null.
And in the example I showed you, I mean, no, it could be fine.
But in the example I showed, I want zero for count min, max and average.
So we can use if no, if you remember that.
So if null replace it with zero.
If null replace max of rating with zero.
Try it again.
And there we are.
We see zero and zero for min and max.
Let's quickly give these new names.
So we'll call this as count.
This one is as Min.
As Max.
Cool.
That's looking better.
The next thing we have to do is get the average so that when simple enough as well.
Same deal, average rating, we'll call it as average.
But once again, we'll run into trouble.
When it comes to Marlin Crawford, we get no, because the average of nothing is nothing.
But I'm going to replace that with zero just to match the slide.
If.
No.
Zero.
Okay, we're almost there.
The final piece here.
I guess we should round this too.
Did I round it in the slide?
Nope, I didn't.
But once again, we'll round it so we can round it first round the result of if no or we could round
average and then do.
If no either one's fine but I'll just do the round on the outside.
Round if no to two places.
Okay.
And there are 0.00 and 8.0 to just two digits.
And now we have the final piece, our status.
So status needs to be active or inactive.
If count is greater than zero, we'll make it active.
So we'll do this.
There's two options.
One is to use case, which is what we've seen before.
So I'll show that first case and end and then we'll say when count of rating, let me capitalize it
to be consistent.
When the count of rating is greater than zero, we'll call you, we'll say then active and then we can
say else inactive OC.
So that should work for us.
We'll call this as status.
Try running this query and we see active for all the users except Marlon Crawford is inactive.
That's correct.
And we're technically done.
That's everything we needed.
But as I said, there's another option.
Case is great when we have two, three, four, five, multiple cases, multiple when then pairs.
But if it's just an if else, which is what we have right now, do this thing.
And if that's not true, then otherwise this thing there's a shorter option.
I'll write it on a second line or a second query.
Instead of having to do this case thing, we can simply say if and then if count of rating is greater
than zero, then after comma will provide that means active.
Otherwise it means inactive.
So we have our condition and then the true portion and the false portion.
And we'll call this as status.
And if I run this one, same exact result.
The difference, of course, is that this allows two choices in.
That's it.
If this is true, we get active.
If it's false, we get inactive.
But with this, with the case statement, I could have multiple cases so we could change it up a tiny
bit if we wanted to work and figure out.
Power users will say if the count of your ratings is greater than or equal to ten, then we'll call
you Oops ten, we'll call you a power user and then we'll have another when this time will say when
count of rating is greater than or equal to zero, then you are.
I didn't spell power user rate active and then else you're inactive because that means you're less than
zero.
So just an example of why a case is more flexible.
It's more work.
We do see now Power user we've got two of them because Domingo and Colt have ten reviews active.
It's anything between zero or one and ten and then zero is inactive.
So that's actually incorrect there because I have greater than or equal to zero.
I want greater than zero, meaning if you're equal to zero, you're in the low portion.
Try it one more time.
And there we go.
So this is above and beyond what we needed.
But I just wanted to illustrate what we could do with the case statement that we can't do with if but
if makes it really short and nice and succinct, if we just have an if else, try this.
Otherwise do this.
If that's how you can express your logic, then if it's very simple.
Otherwise, a case allows us to have multiple cases.
Okay, we've got one more coming up.