Home >Database >Mysql Tutorial >mysql statistical functions and group by

mysql statistical functions and group by

黄舟
黄舟Original
2017-01-16 13:12:111493browse

Five statistical functions (used alone, of little significance, often used in combination with group by)
max maximum select max(shop_price) from goods;
min minimum select min(shop_price) from goods;
sum Sum select sum(shop_price) from goods;
avg Find average select avg(shop_price) from goods;
count How many rows are there in all value rows
count (*) absolute row number null Also included
In addition to count (column name), calculate the number of non-null rows in this column

count uses

mysql> select * from test8;
+------+------+
| id | name |
+------+------+
| 1 | lisi | 
| 2 | NULL | 
+------+------+
mysql> select count(*) from test8;
+----------+
| count(*) |
+----------+
| 2 | 
+----------+
mysql> select count(name) from test8;
+-------------+
| count(name) |
+-------------+
| 1 | 
+-------------+

Query inventory type 4

select sum(goods_number) from goods where cat_id=4;

group by
Count the inventory under each type of group

mysql> select cat_id,sum(goods_number) from goods group by cat_id;
+--------+-------------------+
| cat_id | sum(goods_number) |
+--------+-------------------+
| 2 | 0 | 
| 3 | 203 | 
| 4 | 4 | 
| 5 | 8 | 
| 8 | 61 | 
| 11 | 23 | 
| 13 | 4 | 
| 14 | 9 | 
| 15 | 2 | 
+--------+-------------------+

is not a standard sql statement and cannot be explained logically (each category cat_id contains Many goods_name)
It is not recommended to select goods_name,sum(goods_number) from goods group by cat_id;
Explanation: In select a/b, it must be in group by a/b/c to have no semantic problem

Tips: To understand the query statement, start with the subsequent conditional filtering. First understand the filtering conditions, and then look at the previous execution

The above is the content of mysql statistical functions and group by. For more related content, please Follow the PHP Chinese website (www.php.cn)!


Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article:mysql havingNext article:mysql having