How do aggregate functions work?

Question

MySQL

For example, there is a table:
mysql> SELECT * FROM student;
+----+-----------+------+
| id | name | age |
+----+-----------+------+
| 1 | Dmitry | 18 |
| 2 | Arkady | 18 |
| 3 | Anastasia | 17 |
| 4 | NULL | NULL |
+----+-----------+------+
When using aggregate functions, we get the following:
mysql> SELECT id, name, age, COUNT(*) FROM student;
+----+---------+------+----------+
| id | name | age | COUNT(*) |
+----+---------+------+----------+
| 1 | Dmitry | 18 | 4 |
+----+---------+------+----------+
If count() counts each line, why didn't the result look like this:
+----+-----------+------+----------+
| id | name | age | COUNT(*) |
+----+-----------+------+----------+
| 1 | Dmitry | 18 | 1 |
| 2 | Arkady | 18 | 2 |
| 3 | Anastasia | 17 | 3 |
| 4 | NULL | NULL | 4 |
+----+-----------+------+----------+
How do aggregate functions work in general and how do they differ from ordinary ones?
Please explain the logic of these functions.

Answer 1

In the standard, when using aggregate functions, each field of the selection, ORDER BY and HAVING must either be the result of an aggregate function or be included in GROUP BY. Thus the request resulted by you in the standard is inadmissible.
MySQL, until the latest version, allowed non-standard use of aggregation, choosing the first available value for non-aggregated fields. Starting with version 5.7, MySQL began to require compliance with the standard, and the query you provided will throw an error. You can return to the old settings by disabling the ONLY_FULL_GROUP_BY mode in the settings or by explicitly using the ANY_VALUE() aggregate function on fields that were not aggregated before. Your query in this case would look like

SELECT ANY_VALUE(`id`), ANY_VALUE(`name`), ANY_VALUE(`age`), COUNT(*) 
  FROM `student`;

Since the query does not specify which fields to group by, MySQL groups all rows into one. If you use GROUP BY <list of fields>, then rows with the same values ​​of the fields specified in the list will be grouped. So request

SELECT `age`, COUNT(*) 
  FROM `student`
  GROUP BY `age`;

will issue

+------+----------+
| age  | COUNT(*) |
+------+----------+
|  18  |    2     |
|  17  |    1     |
| NULL |    1     |
+------+----------+

Answer 2

https://professorweb.ru/my/sql-server/2012/level2/...