????????? ???????? ?????????? ???????? ? ???????? ????????? ????????
?????????? ????? ? ???????, ??? ??? ?? ?????? ???????? ?????????? ?? ?????
??????. ??????? COUNT()
?????????? ?????????? ???????? ???????????, ? ? ??
??????? ????? ????????? ????????? ?????? ??? ??????????? ????? ????????:
mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+
????? ?? ??? ????????? ?? ??????? ????? ?????????? ????????. ??? ??????
??????? COUNT()
????? ??????, ??????? ???????? ??????????? ??????? ??
??????????:
mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Benny | 2 |
| Diane | 2 |
| Gwen | 3 |
| Harold | 2 |
+--------+----------+
???????? ???????? ?? ????????????? ??????? GROUP BY
??? ??????????? ????
??????? ?? ??????? ?? ??????????. ??? ???? ??????? ?????? ????? ?? ??????
????????? ?? ??????:
mysql> SELECT owner, COUNT(owner) FROM pet;
ERROR 1140 at line 1: Mixing of GROUP columns (MIN(),MAX(),COUNT()...)
with no GROUP columns is illegal if there is no GROUP BY clause
??????? COUNT()
? GROUP BY
????? ???????? ??????????????? ??????
?????????? ?????????. ? ????????, ??????????? ????, ?? ??????? ? ??????
??????? ?????????? ?????????????? ?????????.
?????????? ???????? ??????? ????:
mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird | 2 |
| cat | 2 |
| dog | 3 |
| hamster | 1 |
| snake | 1 |
+---------+----------+
?????????? ???????? ??????? ????:
mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex | COUNT(*) |
+------+----------+
| NULL | 1 |
| f | 4 |
| m | 4 |
+------+----------+
(? ???? ??????? ??????????? NULL
??????????, ??? ??? ????????? ??????????)
?????????? ???????? ??????? ???? ? ?????? ????:
mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| bird | NULL | 1 |
| bird | f | 1 |
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+------+----------+
??? ????????????? ??????? COUNT()
????? ?? ??????????? ????????? ???
???????. ????????, ?????????? ??????, ? ??????? ??????????? ?????? ????? ?
??????, ???????? ????????? ???????:
mysql> SELECT species, sex, COUNT(*) FROM pet
-> WHERE species = "dog" OR species = "cat"
-> GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
+---------+------+----------+
????? ?????? ? ?????????? ???????? ??????? ???? ? ?????? ?????? ???
???????????, ??? ??????? ????????:
mysql> SELECT species, sex, COUNT(*) FROM pet
-> WHERE sex IS NOT NULL
-> GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| bird | f | 1 |
| cat | f | 1 |
| cat | m | 1 |
| dog | f | 1 |
| dog | m | 2 |
| hamster | f | 1 |
| snake | m | 1 |
+---------+------+----------+
<!--UdmComment-->
User Comments
The GROUP BY clause yields different result between MySQL 4.0.17 and Oracle 8i (didn't check SQL Server).
3 rows in set (0.00 sec)Given a table "items":
mysql> select * from items;
Using the following SELECT statement:
SELECT name, sum(cost) FROM items BROUP BY owner;
Oracle will detect an error saying not a GROUP BY expression
but MySQL gives a result:
2 rows in set (0.01 sec)
Oracle seems to be correct since the "name" column could not really be GROUP BY "owner".
Any comment...
Add your own comment.