Before diving into counting multiple things using expressions, let's have a quick refresher on two basic counting techniques.

- Counting Rows: To count the number of rows in a table, use the
`COUNT(*)`

function. This function counts all rows, regardless of whether they contain null values. - Counting Non-Null Values in Columns: To count the number of non-null values in a column, use the
`COUNT(column_name)`

function. This function counts only non-null values in the specified column.

Let's say we have a table called `rental`

that contains rental information such as rental date, customer ID, and return date. We are interested in knowing how many rentals were done on weekends and how many were done on weekdays.

Next, we can use the `DAYOFWEEK(date)`

function to get the numeric value of the day of the week. This function returns a value between 1 (Sunday) and 7 (Saturday).

SELECT
rental_date, DAYOFWEEK(rental_date)
FROM
rental;

Running this query shows us the rental date and the corresponding day of the week number. This helps us identify the rental dates that fall on weekends.

Now that we have identified the rental dates that fall on weekends, we can use the `COUNT()`

function to count them. However, instead of using the basic `COUNT(*)`

or `COUNT(column_name)`

functions, we will use the `COUNT(if_statement)`

function. This function allows us to count values based on a specified condition.

In our case, we want to count the number of rentals that occurred on weekends. We can use the `IF(condition, value_if_true, value_if_false)`

function to create a column that assigns a value of 1 for rental dates that fall on weekends and a value of null for those that do not.

SELECT
COUNT(IF(DAYOFWEEK(rental_date) IN (1, 7), 1, NULL)) AS weekend_rentals,
COUNT(IF(DAYOFWEEK(rental_date) NOT IN (1, 7), 1, NULL)) AS weekday_rentals,
COUNT(return_date) AS completed_rentals,
COUNT(*) AS total_rentals
FROM
rental;

Running this query shows us the number of rentals that occurred on weekends and weekdays, as well as the total number of completed rentals and the total number of rentals.

While the previous approach is effective, it requires the use of the `COUNT()`

and `IF()`

functions inside each other. An alternative approach is to use the `SUM()`

function instead. This function calculates the sum of a column based on a specified condition.

In our case, we can create a new column for rentals that occurred on weekends and calculate the sum of this column using the `SUM()`

function.

SELECT
SUM(DAYOFWEEK(rental_date) IN (1, 7)) AS weekend_rentals,
SUM(DAYOFWEEK(rental_date) NOT IN (1, 7)) AS weekday_rentals,
COUNT(return_date) AS completed_rentals,
COUNT(*) AS total_rentals
FROM
rental;

This query produces the same results as the previous one but uses the `SUM()`

function instead of `COUNT()`

and calculates the value of either 1 or 0 instead of null.

MySQL allows you to count multiple things using expressions by taking advantage of the way the `COUNT()`

function works. You can use the `IF()`

or `SUM()`

function to create conditions based on your requirements and calculate the sum of values that meet those conditions. This feature helps you quickly and efficiently count various values from a database all at once.