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).
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(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.
SELECTCOUNT(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_rentalsFROMrental;
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
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
SELECTSUM(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_rentalsFROMrental;
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
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.