# How to count multiple things in MySQL using expressions

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.

## Using expressions to count multiple things

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.

### The `DAYOFWEEK`

function

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.

### Using the count and if functions

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.

### Another approach — using the sum and if functions

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.

### Conclusion

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.