Материал подготовлен командой Simple-Server для администраторов VPS и выделенных серверов. Команды и пути проверяйте на тестовой машине перед production.
Кратко о задаче
Счетчик в SQL таблице или «счетчик строк» — это функциональность, которая позволяет подсчитывать количество строк в таблице, удовлетворяющих определенному условию. Это может быть полезно во множестве ситуаций, начиная от определения количества записей в таблице до сложных аналитических запросов на подсчет числа уникальных записей, соответствующих определенным критериям.
Один из самых распространенных примеров использования счетчика строк в SQL — это функция COUNT(). Эта встроенная функция оценивает количество строк в таблице или количество уникальных значений в столбце. Она может быть применена к одному столбцу или ко всей таблице.
Синтаксис команды COUNT() в SQL имеет следующий вид:
SELECT COUNT(column_name)
FROM table_name
WHERE condition-
column_name— это имя столбца, строки которого вы хотите подсчитать; -
table_name— это имя таблицы, откуда берутся данные; -
condition— это необязательное условие, позволяющее фильтровать строки перед подсчетом.
column_name — это имя столбца, строки которого вы хотите подсчитать;
table_name — это имя таблицы, откуда берутся данные;
condition — это необязательное условие, позволяющее фильтровать строки перед подсчетом.
Если ваша цель — подсчитать все строки в таблице, не обращая внимания на их конкретные значения, вы можете применить символ * вместо указания имени колонки, как показано ниже:
SELECT COUNT(*)
FROM table_name;Рассмотрим несколько примеров использования оператора COUNT() в SQL.
В данном руководстве для работы с таблицами мы будем использовать систему управления базами данных (СУБД) SQLite. Вы можете скачать с официального сайта Обозреватель для SQLite — бесплатую программу с открытым исходным кодом, предназначенную для создания и редактирования баз данных SQLite.
Запустите DB Browser for SQLite и перейдите на вкладку SQL, где мы будем писать запросы для создания таблиц и подсчета строк.
Подсчет общего числа записей в таблице
Предположим, у вас есть таблица «Purchases», которая содержит информацию о заказах:
| PurchaseID | BuyerID | Product |
|---|---|---|
| 1 | 101 | Apple |
| 2 | 102 | Banana |
| 3 | 103 | Cherry |
| 4 | 101 | Banana |
| 5 | 104 | Cherry |
Для создания такой таблицы используйте следующий запрос:
CREATE TABLE Purchases (
PurchaseID INTEGER PRIMARY KEY,
BuyerID INTEGER,
Product TEXT
);После запуска SQL-запроса перейдите на вкладку «Структура БД» и убедитесь, что таблица успешно создалась:
Теперь нам надо заполнить нашу таблицу данными, для этого запустите следующий запрос:
INSERT INTO Purchases (PurchaseID, BuyerID, Product) VALUES (1, 101, 'Apple');
INSERT INTO Purchases (PurchaseID, BuyerID, Product) VALUES (2, 102, 'Banana');
INSERT INTO Purchases (PurchaseID, BuyerID, Product) VALUES (3, 103, 'Cherry');
INSERT INTO Purchases (PurchaseID, BuyerID, Product) VALUES (4, 101, 'Banana');
INSERT INTO Purchases (PurchaseID, BuyerID, Product) VALUES (5, 104, 'Cherry');На вкладке «Данные» отображается наша заполненная таблица:
Теперь, если вы хотите узнать общее количество заказов, можно использовать простой COUNT с символом *:
SELECT COUNT(*)
FROM Purchases;Это подсчитает и вернет общее число записей в таблице «Purchases». Результатом выполнения это запроса будет 5.
Подсчет общего числа уникальных записей в столбце
Допустим, у вас есть таблица «Buyers» и вы хотите узнать, сколько уникальных стран представлено среди адресов ваших клиентов:
| BuyerID | FirstName | BuyerCountry |
|---|---|---|
| 101 | Ivan | Russia |
| 102 | Alice | USA |
| 103 | Bob | USA |
| 104 | John | Canada |
| 105 | Petr | Russia |
Аналогично тому, как мы создавали таблицу «Purchases», создадим таблицу «Buyers»:
CREATE TABLE Buyers (
BuyerID INTEGER PRIMARY KEY,
FirstName TEXT,
BuyerCountry TEXT
);
INSERT INTO Buyers (BuyerID, FirstName, BuyerCountry) VALUES (101, 'Ivan', 'Russia');
INSERT INTO Buyers (BuyerID, FirstName, BuyerCountry) VALUES (102, 'Alice', 'USA');
INSERT INTO Buyers (BuyerID, FirstName, BuyerCountry) VALUES (103, 'Bob', 'USA');
INSERT INTO Buyers (BuyerID, FirstName, BuyerCountry) VALUES (104, 'John', 'Canada');
INSERT INTO Buyers (BuyerID, FirstName, BuyerCountry) VALUES (105, 'Petr', 'Russia');В таблице видно, что колонка BuyerCountry включает в себя несколько стран, но некоторые страны повторяются (например, Russia).
Если вы хотите подсчитать количество уникальных стран, то есть исключить повторяющиеся значения, вы можете использовать COUNT с ключевым словом DISTINCT:
SELECT COUNT(DISTINCT BuyerCountry)
FROM Buyers;Это подсчитает и вернет число уникальных стран в столбце BuyerCountry таблицы «Buyers». В результате выполнения этого запроса будет выведено 3.
Подсчет с условием (фильтрацией данных)
Иногда вы хотите посчитать только строки, соответствующие определенному условию. Заказы в этой таблице сделаны клиентами из разных стран:
| PurchaseID | BuyerID | Product | BuyerCountry |
|---|---|---|---|
| 1 | 101 | Apple | Russia |
| 2 | 102 | Banana | USA |
| 3 | 103 | Cherry | USA |
| 4 | 101 | Banana | Russia |
| 5 | 104 | Cherry | Russia |
У нас уже создана такая таблица «Purchases», но в ней отсутствует столбец BuyerCountry, добавим его:
ALTER TABLE Purchases
ADD BuyerCountry TEXT;
UPDATE Purchases
SET BuyerCountry = 'Russia'
WHERE BuyerID IN (101, 104);
UPDATE Purchases
SET BuyerCountry = 'USA'
WHERE BuyerID IN (102, 103);Предположим, вы хотите узнать, сколько заказов было сделано клиентами из России. Вы можете использовать COUNT с условием WHERE:
SELECT COUNT(*)
FROM Purchases
WHERE BuyerCountry = 'Russia';Этот SQL-запрос со счетчиком подсчитает и вернет только те заказы, которые были сделаны клиентами из России. Результатом выполнения этого запроса будет 3.
Подсчет с группировкой данных (GROUP BY)
Когда нужно подсчитать количество записей в определенной группе, используется функция GROUP BY.
| PurchaseID | BuyerID | Product |
|---|---|---|
| 1 | 101 | Apple |
| 2 | 102 | Banana |
| 3 | 103 | Cherry |
| 4 | 101 | Banana |
| 5 | 104 | Cherry |
| 6 | 101 | Apple |
Изменим нашу имеющуюся таблицу «Purchases», добавим новую строку:
INSERT INTO Purchases (PurchaseID, BuyerID, Product)
VALUES
(6, 101, 'Apple');Например, вы хотите узнать, сколько заказов было сделано каждым клиентом:
SELECT BuyerID, COUNT(*)
FROM Purchases
GROUP BY BuyerID;Это запрос сначала группирует заказы по BuyerID, а дальше подсчитывает количество заказов для каждого клиента. В результате выполнения этого запроса будет выведено:
| BuyerID | COUNT(*) |
|---|---|
| 101 | 3 |
| 102 | 1 |
| 103 | 1 |
| 104 | 1 |
Совместное использование функции COUNT и AVG
Предположим, у вас есть таблица «Ratings», которая содержит оценки товаров пользователями:
| RatingID | ProductID | UserID | Rating |
|---|---|---|---|
| 1 | 101 | 1 | 4.0 |
| 2 | 102 | 2 | 3.5 |
| 3 | 101 | 3 | 4.5 |
| 4 | 103 | 1 | 5.0 |
| 5 | 101 | 2 | 3.0 |
| 6 | 102 | 3 | 4.5 |
CREATE TABLE Ratings (
RatingID INT PRIMARY KEY,
ProductID INT,
UserID INT,
Rating DECIMAL(2,1)
);
INSERT INTO Ratings (RatingID, ProductID, UserID, Rating)
VALUES
(1, 101, 1, 4.0),
(2, 102, 2, 3.5),
(3, 101, 3, 4.5),
(4, 103, 1, 5.0),
(5, 101, 2, 3.0),
(6, 102, 3, 4.5);В этой таблице каждая строка представляет оценку конкретного товара отдельным пользователем.
Теперь вы хотите узнать, сколько всего оценок было поставлено каждому товару и какова их средняя оценка. Вы можете использовать следующий запрос для этого:
SELECT ProductID, COUNT(*), AVG(Rating)
FROM Ratings
GROUP BY ProductIDВ результате выполнения этого запроса будет выведено:
| ProductID | COUNT(*) | AVG(Rating) |
|---|---|---|
| 101 | 3 | 3.83 |
| 102 | 2 | 4.0 |
| 103 | 1 | 5.0 |
Таким образом, вы видите, что товар с ID 101 был оценен 3 раза и его средняя оценка 3.83, товар с ID 102 был оценен 2 раза со средней оценкой 4.0, и товар с ID 103 был оценен 1 раз со средней оценкой 5.0.
Использование COUNT c HAVING
Иногда нужно отфильтровать результаты подсчета. Представим, что у нас есть таблица, где содержатся оценки пользователей для различных товаров.
| RatingID | ProductID | UserID | Rating |
|---|---|---|---|
| 1 | 101 | 1 | 4.0 |
| 2 | 101 | 2 | 3.5 |
| … | … | … | … |
| 101 | 101 | 101 | 4.5 |
| 102 | 102 | 102 | 5.0 |
| … | … | … | … |
| 300 | 102 | 300 | 4.8 |
| 301 | 103 | 101 | 3.9 |
В этой таблице мы видим, что каждый отзыв представлен в отдельной строке с уникальным RatingID и каждому ProductID соответствуют разные Rating.
Если предположить, что мы хотим проверить, какие продукты получили больше 100 оценок от пользователей, то мы можем использовать следующий запрос:
SELECT ProductID, COUNT(*)
FROM Ratings
GROUP BY ProductID
HAVING COUNT(*) > 100;В нашем случае результат будет равен 0, так как в нашей текущей таблице «Ratings» нет ни одного продукта, который получил бы более 100 оценок. Но, если ваша таблица содержит большой объем данных и вы хотите узнать, какие товары получили больше 100 оценок от пользователей, то вышеприведенный запрос предоставит вам ответ.
Важно отметить, что HAVING применяется после GROUP BY, а не перед, как в случае с WHERE.
Использование COUNT в сочетании с JOIN
У нас есть две таблицы: «Orders» и «Customers».
| OrderID | ProductID | CustomerID |
|---|---|---|
| 1 | 201 | 1 |
| 2 | 202 | 1 |
| 3 | 203 | 2 |
| 4 | 204 | 3 |
Запрос для создания таблицы «Orders»:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
ProductID INT,
CustomerID INT
);
INSERT INTO Orders (OrderID, ProductID, CustomerID)
VALUES
(1, 201, 1),
(2, 202, 1),
(3, 203, 2),
(4, 204, 3);| CustomerID | FirstName | CustomerCountry |
|---|---|---|
| 1 | Ivan | Russia |
| 2 | Jack | USA |
| 3 | John | Canada |
Запрос для создания таблицы «Customers»:
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(255),
CustomerCountry VARCHAR(255)
);
INSERT INTO Customers (CustomerID, FirstName, CustomerCountry)
VALUES
(1, 'Ivan', 'Russia'),
(2, 'Jack', 'USA'),
(3, 'John', 'Canada');Нас интересует присоединение (JOIN) двух таблиц и подсчет (COUNT) числа строк, соответствующих определенному условию:
SELECT COUNT(*)
FROM Orders
LEFT JOIN Customers ON Customers.CustomerID = Orders.CustomerID
WHERE Customers.CustomerCountry = 'Russia';В запросе используется LEFT JOIN для объединения таблиц по полю CustomerID. Смысл LEFT JOIN состоит в том, что в итоговую таблицу будут включены все строки из левой таблицы (Orders), даже если для них не найдется соответствия в правой таблице (Customers).
Тем не менее, за счет условия WHERE в итоговый отчет попадут только такие строки, для которых в поле Country стоит Russia. То есть, будут учтены только заказы, сделанные клиентами из России.
Таким образом, этот запрос позволяет получить общее число заказов, выполненных клиентами из России, с использованием данных об этих заказах и клиентах из двух разных таблиц.
Сортировка данных с использованием функции ORDER BY
Представим, у вас есть таблица «Employess» с информацией о сотрудниках:
| EmployeeID | FirstName | LastName | Salary |
|---|---|---|---|
| 1 | Ivan | Petrov | 55000 |
| 2 | Petr | Sidorov | 75000 |
| 3 | Alexey | Ivanov | 70000 |
| 4 | Dmitry | Sidorov | 80000 |
| 5 | Sergey | Petrov | 90000 |
Запрос для создания таблицы:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(255),
LastName VARCHAR(255),
Salary INT
);
INSERT INTO Employees (EmployeeID, FirstName, LastName, Salary)
VALUES
(1, 'Ivan', 'Petrov', 55000),
(2, 'Petr', 'Sidorov', 75000),
(3, 'Alexey', 'Ivanov', 70000),
(4, 'Dmitry', 'Sidorov', 80000),
(5, 'Sergey', 'Petrov', 90000);Мы хотим узнать, сколько сотрудников в представленной таблице получают зарплату более 60000, а также отобразить это количество для каждой отдельной зарплаты в порядке убывания зарплаты. Для достижения этой цели мы можем написать следующий запрос:
SELECT Salary, COUNT(*)
FROM Employees
WHERE Salary > 60000
GROUP BY Salary
ORDER BY Salary DESC;Этот запрос подсчитает количество сотрудников для каждой отдельной зарплаты, которая выше 60000, и отобразит эти зарплаты в порядке убывания. Количество сотрудников для каждого значения будет показано рядом с соответствующим значением зарплаты:
| Salary | COUNT(*) |
|---|---|
| 90000 | 1 |
| 80000 | 1 |
| 75000 | 1 |
| 70000 | 1 |
Подсчет с использованием двух условий (AND и OR)
Допустим, у вас есть таблица «Sales», которая содержит информацию о продажах:
| SaleID | Product | Price | Quantity |
|---|---|---|---|
| 1 | Apple | 20 | 5 |
| 2 | Banana | 15 | 10 |
| 3 | Cherry | 10 | 15 |
| 4 | Apple | 20 | 20 |
| 5 | Banana | 15 | 25 |
Запрос для создания таблицы:
CREATE TABLE Sales (
SaleID INT PRIMARY KEY,
Product VARCHAR(255),
Price INT,
Quantity INT
);
INSERT INTO Sales (SaleID, Product, Price, Quantity)
VALUES
(1, 'Apple', 20, 5),
(2, 'Banana', 15, 10),
(3, 'Cherry', 10, 15),
(4, 'Apple', 20, 20),
(5, 'Banana', 15, 25);Мы хотим узнать, сколько раз продукт Apple был продан в количестве меньше 10 или продукт Banana в количестве больше 20. Для этого можем использовать COUNT в сочетании с условиями AND и OR:
SELECT COUNT(*)
FROM Sales
WHERE (Product = 'Apple' AND Quantity < 10) OR (Product = 'Banana' AND Quantity > 20)Этот запрос подсчитает количество продаж по указанным условиям. Результатом выполнения этого запроса будет 2.
Особенности использования
После изучения представленных практических примеров у вас не должно возникнуть вопроса: как сделать счетчик в SQL? В завершение обсудим некоторые особенности применения функции COUNT(), чтобы вы могли свободно и без препятствий использовать ее в своей работе:
-
Работа с
NULL-значениями: функцияCOUNT(column_name)в подсчет не включает строки сNULL-значениями. Если вам нужно учесть эти строки, следует использоватьCOUNT(*). -
Использование с
DISTINCT: еслиCOUNT()используется в сочетании сDISTINCT, это приводит к подсчету только уникальных значений в выбранной колонке. -
Применение с
GROUP BY:COUNT()часто используют с операторомGROUP BY, чтобы подсчитывать количество строк в каждой группе. -
Целочисленный результат: независимо от типа данных аргумента, функция
COUNT()всегда возвращает целочисленное значение. -
Использование в подзапросах:
COUNT()может быть использован в подзапросах для получения количества строк, которые соответствуют определенным критериям. -
Использование в триггерах:
COUNT()может быть использован в триггерах SQL для подсчета количества измененных строк. -
Работа с большими таблицами: когда используется
COUNT()на большом объеме данных, запрос может занять значительное время. В таких случаях следует подумать о проведении оптимизации запросов. -
Работа с совместными (
JOIN) таблицами: когда используются запросы с запутанными связями между таблицами, подсчет может стать сложной задачей. Выполнение таких запросов требует от вас понимания структуры базы данных.
Работа с NULL-значениями: функция COUNT(column_name) в подсчет не включает строки с NULL-значениями. Если вам нужно учесть эти строки, следует использовать COUNT(*).
Использование с DISTINCT: если COUNT() используется в сочетании с DISTINCT, это приводит к подсчету только уникальных значений в выбранной колонке.
Применение с GROUP BY: COUNT() часто используют с оператором GROUP BY, чтобы подсчитывать количество строк в каждой группе.
Целочисленный результат: независимо от типа данных аргумента, функция COUNT() всегда возвращает целочисленное значение.
Использование в подзапросах: COUNT() может быть использован в подзапросах для получения количества строк, которые соответствуют определенным критериям.
Использование в триггерах: COUNT() может быть использован в триггерах SQL для подсчета количества измененных строк.
Работа с большими таблицами: когда используется COUNT() на большом объеме данных, запрос может занять значительное время. В таких случаях следует подумать о проведении оптимизации запросов.
Работа с совместными (JOIN) таблицами: когда используются запросы с запутанными связями между таблицами, подсчет может стать сложной задачей. Выполнение таких запросов требует от вас понимания структуры базы данных.
Нужен сервер для практики? Арендуйте VPS/VDS в России — root-доступ, NVMe, DDoS-защита и поддержка 24/7.