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.