In this lesson, we're going to look at how to calculate totals in the database using conditional aggregates. To illustrate this, I've set up this demo app which tracks customer feature requests. Each feature request has a title, a status, and a bunch of comments associated with it. Now, imagine a new requirement comes in to add some status badges to the top of the page to show how many feature requests are in each step of the process. And, to get us going, the design work has already been completed. Let's jump into our Features Blade view and uncomment that HTML. And now, if we hit refresh in the browser, we can see our new status badges. However, if we look at our FeaturesController, we can see that we're just passing in some

And now, if we hit refresh in the browser, we can see our new status badges. However, if we look at our FeaturesController, we can see that we're just passing in some placeholder values right now. So, how do we go about calculating these totals in the most efficient way? One solution is to just run separate database queries to calculate the total for each status. Let's try that. Let's just copy the status and then write the query for each one. Feature::where the status is equal to our status, and we'll just title case that, and then we'll just run a count on that. If we hit refresh in the browser, we can see that we're now getting our totals.

That's when this starts to become a problem. Let me show you a technique that I like to use in situations like this. Let's jump into TablePlus first and write some SQL. You see, there's actually a way to calculate these totals in a single database query. You're probably familiar with writing count aggregate queries like this. And if we run that, we can see that we have 60 features in our database. However, what you might not realize is that you can actually put conditions within aggregate functions. Let's update this query to do that. Within our count, we're going to add a case statement.

Let's update this query to do that. Within our count, we're going to add a case statement. case when status equals requested, then 1, end. And if we run that again, we get 44. And if we look back in our app, we can see that that's correct. We have 44 requested feature requests. So let's add another one. count case when status equals planned, then 1, end. And if we run this, we get the second count, which is 6, which is correct. And then we'll do this for the last one as well.

And if we run this, we get the second count, which is six, which is correct. And then we'll do this for the last one as well. Count case when status equals completed, then one, end. 44 plus six plus 10 equals 60. Pretty cool. However, all the columns right now have the name of count. So that's not going to work very well in our app. So let's add an alias to each column. As requested, as planned, and as completed. And now if we run this again, we can see that each one of our columns has an appropriate

As requested, as planned, and as completed. And now if we run this again, we can see that each one of our columns has an appropriate column name. Okay, let's update our FeaturesController to run this query. Let's remove our individual queries and start with a new single query. Feature::base. And the reason why I use base here is because we don't want the Feature model to return back, but instead we want a collection of the different totals. Next, we'll selectRaw, and then we'll get a count case when status equals requested, then 1, end.

Next, we'll select raw, and then we'll get a count case when status equals requested, then one, end. And then we'll just alias that to the requested column name. And now we can duplicate this twice for our two other statuses. Planned and completed. Planned and completed. And finally, we need to run the query. And we're just going to use the first method for this, since we know we only get one row back for this query. And if we hit refresh in the browser, we can see that our status badges are still correct.

back for this query. And if we hit refresh in the browser, we can see that our status badges are still correct. And we can see that we're now down to three database queries. We're now only running one query to get our status totals. Nice. Before we wrap up this lesson, here's a little bonus for you Postgres users. You can achieve the same thing in Postgres using filter clauses. Let's give this a try. Let's remove these existing queries, except we'll keep the column aliases at the end. Now we'll count *, filter, where the status equals, and then we just copy and paste in

A nice perk of this approach is that filter clauses can actually be faster than the case statements. So if you're using Postgres, definitely consider this method instead.