MySQL COUNT Function

The MySQL COUNT function is a built-in aggregate function used to count the number of rows in a table that meet a specified condition. 

The syntax for the COUNT function is: 

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

Here, column_name is the name of the column you want to count the number of rows for. If you want to count all the rows in the table, you can use the asterisk symbol (*) instead of the column name.

The table_name is the name of the table you want to count rows from.

The condition is an optional parameter that specifies a condition that must be met for the rows to be counted.

For example, to count the number of rows in a table called "employees" where the employee's age is greater than 30, you can use the following query: 

SELECT COUNT(*)
FROM employees
WHERE age > 30;

If you only want to count the number of rows where the age is greater than 30 and the department is "Sales," you can modify the query as follows: 

SELECT COUNT(*)
FROM employees
WHERE age > 30 AND department = 'Sales';

What is in our table:

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+
| s_id | city   | store_name | product         | available | price |
+------+--------+------------+-----------------+-----------+-------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 |
|    2 | London | London_2   | Gold PSU        |        75 |   100 |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 |
+------+--------+------------+-----------------+-----------+-------+
8 rows in set (0.00 sec)

mysql>

COUNT(*) example:

mysql> SELECT COUNT(*) AS NumOfRows FROM Stores;
+-----------+
| NumOfRows |
+-----------+
|         8 |
+-----------+
1 row in set (0.04 sec)

mysql>

The result of the query is a single row with a single column "NumOfRows" which contains the total number of rows in the "Stores" table. In this case, the result is 8.

mysql> SELECT COUNT(s_id) AS NumOfRows FROM Stores;
+-----------+
| NumOfRows |
+-----------+
|         8 |
+-----------+
1 row in set (0.00 sec)

mysql>

Our instruction selects the total number of non-null values in the "s_id" column of the "Stores" table and assigns it an alias "NumOfRows". 

Since all rows in the "Stores" table have a non-null value in the "s_id" column, the result of the query is the same as the previous query, i.e., a single row with a single column "NumOfRows" which contains the total number of rows in the "Stores" table.

COUNT(*) and WHERE combination: 

mysql> SELECT COUNT(*) AS GoldReport FROM Stores WHERE product LIKE "%ol%";
+------------+
| GoldReport |
+------------+
|          2 |
+------------+
1 row in set (0.00 sec)

mysql>

Query selects the total number of rows in the "Stores" table where the "product" column contains the string "ol" (with any characters before or after "ol"), and assigns it an alias "GoldReport". The LIKE operator with the "%" wildcard is used to match any string that contains "ol" (with any characters before or after it).

COUNT(*), WHERE and LIKE combination:

mysql> SELECT COUNT(*) AS ZeroReport FROM Stores WHERE price LIKE "%0%";
+------------+
| ZeroReport |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

mysql>

This MySQL query selects the total number of rows in the "Stores" table where the "price" column contains the number "0" (with any characters before or after "0"), and assigns it an alias "ZeroReport". 

The LIKE operator with the "%" wildcard is used to match any string that contains "0" (with any characters before or after it).

NOT Like example:

mysql> SELECT COUNT(*) AS ZeroReport FROM Stores WHERE price NOT LIKE "%0%";
+------------+
| ZeroReport |
+------------+
|          4 |
+------------+
1 row in set (0.00 sec)

mysql>

The NOT LIKE operator with the "%" wildcard is used to match any string that does not contain "0" (with any characters before or after it).

The result is 4, which means that there are 4 rows in the "Stores" table where the "price" column does not contain the number "0" (with any characters before or after "0").