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.