3.3.4.8 Подсчет строк

Базы данных часто используются для получения ответа на вопросы типа: ``как часто данные определенного типа встречаются в таблице?'' Вам, например, может понадобиться узнать общее количество животных, или то, сколько животных имеется у каждого из владельцев, или провести статистические исследования на базе хранящейся информации.

Процедура подсчета количества животных в сущности идентична подсчету количества строк в таблице, так как на каждое животное приходится по одной записи. Функция 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 |
+---------+------+----------+

User Comments

I would reccomend the SQL tutorial at

http://www.sqlcourse.com

but use your new mySQL database in place of their
online database, as theirs often doesn't work!

I would very much like to see some examples
with "NULL" Variables used in sort columns.. I
noticed some interesting things trying to get my
Date column to return NULL variables last, but
keep an ascending order..

I did ORDER BY due DESC and ORDER BY due, the
results were predictable, NULL came last in the
first one, and first in the second one.. But i
wanted NULL to come last, but the rest of the
data to have an ascending order.

I tried SELECT to_days(now())-to_days(due) as
test FROM jobs ORDER BY test and ORDER BY test
DESC.. The interesting thing, was that the NULL
always showed up first in this list...

Needless to say, im still trying to work around
this..

This tutorial also assumes you have used SQL
before:- a primer on the concepts might be
useful, and an explanation of how to change
column headers etc would be nice from the start.

It would be GREAT to see an example of count using
2 tables. For example one table contains artists and
another table contains a list of works and use count
to get a list of all artists and how many works they
have done (including artists who have no works ie.
count = 0 )

Use this example if you want to make a "word
sensitive" search in any field containing text:

SELECT count(*) FROM table WHERE dateused >='yyyy-
mm-dd hh:mm:ss' AND dateused <='yyyy-mm-dd
hh:mm:ss'
AND field_name REGEXP "[^a-zA-Z]brbr[^a-zA-Z]
|^brbr[^a-zA-Z]"

In version 3.23.46 DISTINCT only works with COUNT
([DISTINCT] ...), not with AVG, SUM, MIN or MAX
aggregate functions.

I've been having problems with counting rows from
to different tables in one query.
"SELECT count(tabel1.column), count
(tabel2.column) FROM tabel1,tabel2"
takes post1*post2 for some reason.

I would like to see this example too!

I have NO idea if this is the proper way to do this
(READ: This has not been heavily tested so don't
flame me!), but I figured out a way to do what
Glenn mentioned a few comments back.

Let's say you have a table with a list of artists
like so:


+-----------+-------------+
| artist_id | artist_name |
+-----------+-------------+
| 1 | Rembrandt |
| 2 | Raphael |
| 3 | Picasso |
| 4 | O'Keeffe |
+-----------+-------------+


And a list of paintings that are associated with
these artists like so:


+-------------+--------------------------------+-----------+
| painting_id | painting_name |
artist_id |
+-------------+--------------------------------+-----------+
| 1 | The raising of Lazarus |
1 |
| 2 | Bathsheba at Her Bath |
1 |
| 3 | The return of the prodigal son |
1 |
| 4 | The nymph Galatea |
2 |
| 5 | The Burning of the Borgo |
2 |
| 6 | Don Quixote |
3 |
+-------------+--------------------------------+-----------+

In order to view the artists and their number of
works *including* those that have no works, I
needed to do the following query:
select
artist.artist_name,count(painting.painting_id)
from artist left join painting on
artist.artist_id=painting.artist_id group by
artist.artist_id;

In order to get the result:


+-------------+-----------------------------+
| artist_name | count(painting.painting_id) |
+-------------+-----------------------------+
| Rembrandt | 3 |
| Raphael | 2 |
| Picasso | 1 |
| O'Keeffe | 0 |
+-------------+-----------------------------+


I hope that posted properly and I hope this helps.
If
it doesn't work for you, I'd be interested to hear.

Counting rows from different tables in one query:

SELECT COUNT(DISTINCT table1.ID) AS table1, COUNT(DISTINCT table2.ID) AS table2, COUNT(DISTINCT table3.ID) AS table3 FROM table1, table2, table3

DISTINCT in COUNT() is supported in version 3.23.2 and upper.

I do manage a large white page system with millions
of records that is in hosting at <a
href='http://www.wide.it'>wide hosting</a> and
when I do counting I would like to set a row limit: is
that possible to do like:

SELECT COUNT(*) FROM WHITEPAGE WHERE NAME
LIKE 'JOHN%' LIMIT 1000 ?

To avoid problems with a field named Count(*) if
you are using this to create tables or anything else,
use a query similar to:
CREATE TABLE proxy.webhits SELECT Domain,
User_Name, Site, Count(*) AS Hits FROM rawlog
GROUP BY User_Name, Site
Take special note of the AS. Because once
a table
with a Count(*) column has been created, you
cannot rename the column because it contains a
reserved word. Which can cause other problems.


This method should be included in these instructions,
because, as a novice, I just spent half an hour
digging through webpages that all assume people
already know that or something.

Adam Newman correctly points out that it's a good
idea to use AS to rename your column when using
CREATE TABLE ... SELECT ... However, if you have
named your column something like COUNT
(DISTINCT uid), you can rename it like this:

ALTER TABLE newtable
CHANGE `COUNT(DISTINCT id)` num_uids INT;

Add your own comment.