Как посчитать количество null в sql

SQL Базовый №8. NULL, агрегаты, ошибки, порядок выполнения операций

В этом уроке вам понадобятся таблицы religion и bike_sales, которые были созданы в предыдущих уроках курса.

Значение NULL

Если в функцию COUNT передать столбец, то вернется количество не NULL значений.

-- Посчитать все не null значения select count(metro_station) from religion

Если в функцию COUNT передать всю таблицу, то вернется количество строк.

-- Посчитать все строки даже если в каких-то столбцах находится null -- И даже если все значения в строке - это null select count(*) from religion

Добавим строку, которая полностью будет состоять из NULL. После INSERT еще раз посчитайте количество строк.

-- Добавим строку, в которой все значения null insert into religion values (null, null, null, null, null, null, null, null, null, null, null)

Теперь удалим созданную строку. Убедились, что COUNT учитывает даже полностью NULL строки. Теперь строка нам больше не нужна.

-- Удалим созданную полностью null строку delete from religion where id is null

С помощью IS NULL можно посчитать количество NULL.

-- Посчитать строки, где metro_station is null select count(*) from religion where metro_station is null

С помощью IS NOT NULL можно посчитать количество не NULL.

-- Посчитать строки, где metro_station is not null select count(*) from religion where metro_station is not null

Запрос к таблице из другой схемы

На панели инструментов мы всегда можем увидеть с каком схемой в данный момент работаем.

Это не значит, что мы можем выполнять запросы только к этой схеме. Просто к таблицам этой схемы можно обращаться без указания схемы. Если нужно сделать запрос к сущности из другой схемы, то перед именем таблицы указывается схема, в которой она находится.

-- Запрос к другой схеме select * from bikes.bike_sales

Применимость агрегатных функций к разным типам данных

Функции MIN, MAX, COUNT, SUM применимы к значениям с числовыми типами данных. Это очевидно.

-- Агрегация MIN, MAX, COUNT, SUM для числа select min(profit) as minimum ,max(profit) as maximum ,count(profit) as cnt ,sum(profit) as summa from bikes.bike_sales

К дате можно применить MIN, MAX, COUNT.

select min(order_date) as minimum ,max(order_date) as maximum ,count(order_date) as cnt --,sum(order_date) as summa from bikes.bike_sales

К тексту применимы MIN, MAX, COUNT. Если отсортировать значения по алфавиту, то первое из них будет минимальным, а последнее максимальным.

select min(order_month) as minimum ,max(order_month) as maximum ,count(order_month) as cnt --,sum(order_month) as summa from bikes.bike_sales

Рекомендации по форматированию кода

• При перечислении полей запятая ставится вначале строки, а не в конце
• Логические блоки нужно обозначать отступами
• После WHERE пишется 1 = 1
• Функции, ключевые слова вводятся большими буквами

-- Рекомендации по форматированию кода SELECT product_category ,product_subcategory ,SUM(revenue) AS revenue ,SUM(cost) AS cost ,SUM(profit) AS profit FROM bikes.bike_sales WHERE 1 = 1 AND order_year = 2015 AND customer_country IN ('United States', 'Canada') GROUP BY product_category ,product_subcategory HAVING SUM(profit) >= 30000 ORDER BY profit DESC

Ошибки в коде

Если неправильно ввести имя столбца, то вернется ошибка 42703. В описании ошибки также можно увидеть строку, в которой она находится и позицию. Позиция считается с начала выделения. В примере выделение начинается со слова SELECT. Буква «S» находится на первой позиции.

По такому же принципу выявляются и исправляются другие ошибки, например:

• Пропущена запятая
• Использовано неверное ключевое слово, например, FOR вместо FROM

Порядок выполнения операций

Операции выполняются не в том порядке, в котором они написаны. Разберем на примере предыдущего запроса.

SQL Базовый №8. NULL, агрегаты, ошибки, порядок выполнения операций was last modified: 15 апреля, 2023 by Admin

SQL функция COUNT

В этом учебном материале вы узнаете, как использовать SQL функцию COUNT с синтаксисом и примерами.

Описание

SQL функция COUNT используется для подсчета количества строк, возвращаемых в операторе SELECT.

Синтаксис

Синтаксис для функции COUNT в SQL.

SELECT COUNT(aggregate_expression)
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]];

Или синтаксис для функции COUNT при группировке результатов по одному или нескольким столбцам.

SELECT expression1, expression2, . expression_n,
COUNT(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, . expression_n
[ORDER BY expression [ ASC | DESC ]];

Параметры или аргумент

expression1 , expression2 , . expression_n Выражения, которые не инкапсулированы в функции COUNT и должны быть включены в предложение GROUP BY в конце SQL запроса aggregate_expression Это столбец или выражение, чьи ненулевые значения будут учитываться tables Таблицы, из которых вы хотите получить записи. В предложении FROM должна быть указана хотя бы одна таблица WHERE conditions Необязательный. Это условия, которые должны быть выполнены для выбора записей ORDER BY expression Необязательный. Выражение, используемое для сортировки записей в наборе результатов. Если указано более одного выражения, значения должны быть разделены запятыми ASC Необязательный. ASC сортирует результирующий набор в порядке возрастания по expressions . Это поведение по умолчанию, если модификатор не указан DESC Необязательный. DESC сортирует результирующий набор в порядке убывания по expressions

Пример — функция COUNT включает только значения NOT NUL

Не все это понимают, но функция COUNT будет подсчитывать только те записи, в которых expressions НЕ равно NULL в COUNT( expressions ). Когда expressions является значением NULL, оно не включается в вычисления COUNT. Давайте рассмотрим это дальше.

В этом примере у нас есть таблица customers со следующими данными:

customer_id	first_name	last_name	favorite_website
4000	Justin	Bieber	google.com
5000	Selena	Gomez	bing.com
6000	Mila	Kunis	yahoo.com
7000	Tom	Cruise	oracle.com
8000	Johnny	Depp	NULL
9000	Russell	Crowe	google.com

Введите следующий запрос SELECT, которая использует функцию COUNT.

Хитрости count() в SQL

Функция count(), если с ней правильно обращаться, может творить маленькие чудеса.

Допустим, есть таблица usr с платежами клиентов, хранящая идентификаторы клинтов и суммы платежей:

ID	PRICE
1	1
1	2
1	3
2	1
2	2
2	3

Нужно посчитать, сколько платежей выполнил каждый клиент.

Решение этой задачи очевидно:

SELECT id, count(*)
FROM usr
GROUP BY id

ID	COUNT
1	3
2	3

Теперь усложним задачу. Помимо количества платежей вообще, посчитаем, сколько было платежей, имеющих некий отличительный признак.

Отличительным признаком может быть что угодно. Допустим, это будет размер платежа. Посчитаем, сколько было платежей, размер которых превышает 1 рубль.

Обратите внимание — и количество платежей вообще и количество платежей больше рубля посчитаем одновременно, _одним_ запросом.

Вот тут решение становится уже не таким очевидным.

Понятно, что тут нам нужна все та же функция count, только считающая не все строки в группе, а те, которые удовлетворяют условию price>1.

Т.е. запрос должен быть типа такого:

SELECT id, count(*), count(price>1)
FROM usr
GROUP BY id

Конечно, в таком виде запрос не сработает. Хотя выражение price>1, переданное функции count(), само по себе будет вычислено правильно, на функцию count() результат этого вычисления никак не повлияет.

Дело в том, что count() просто не понимает булевых выражений. Вместо этого функция count() понимает значения NULL.

Функция count(expr) всегда считает только те строки в группе, у которых результатом выражения expr является NOT NULL. (Исключением из этого правила является использование функции count() со звездочкой в качестве аргумента — count(*). В этом случае считаются все строки, вне зависимости от того, NULL они или не NULL.)

Соответственно, для решения нашей задачи в функцию count() вместо булева выражения нужно подставить выражение, возвращающее NULL или NOT NULL. Если строку нужно посчитать — выражение должно возвращать NOT NULL.

В общем виде запрос будет выглядеть так:

SELECT id, count(*), count(XXX)
FROM usr
GROUP BY id

В общем и целом, XXX — это условное выражение, возвращающее NOT NULL для тех строк, которые нужно посчитать. В нашем случае это строки, удовлетворяющие условию price>1.

Напишем это выражение:

CASE
WHEN price>1 THEN 1
ELSE NULL
END

Как видите, если price будет больше 1, то будет возвращена единица. В противном случае будет возвращен NULL. (На самом деле совершенно не важно, какое значение возвращать, если price>1. Главное — возвратить NOT NULL.)

Теперь напишем весь запрос целиком:

SELECT id, count(*), count(CASE
WHEN price>1 THEN 1
ELSE NULL
END)
FROM usr
GROUP BY id

ID	COUNT	COUNT
1	3	2
2	3	2

Как посчитать количество null в sql

А, вот интересно, а что будет происходить с функциями типа AVG(), MIN(), MAX(), SUM(), COUNT(), если значение столбца будет содержать значение нашего доброго старого знакомого — NULL? По правилам ANSI/ISO сказано, что «агрегатные функции игнорируют значение NULL»! Вот если честно, ну достал этот NULL, просто сил нет! 🙂 Итак давайте проверим, на примере, что же будет происходить. Дадим вот такой запрос:

SELECT COUNT(*), COUNT(SALES), COUNT(QUOTA) FROM SALESREPS /

SQL> SELECT COUNT(*), COUNT(SALES), COUNT(QUOTA) 2 FROM SALESREPS 3 / COUNT(*) COUNT(SALES) COUNT(QUOTA) ---------- ------------ ------------ 11 11 10

Странный какой то результат? В чем тут вопрос. Таблица вроде одна, а вот значения в запросе разные. А все дело в том что, одно из полей QUOTA — содержит NULL. От сюда и вся не разбериха. Функция COUNT вида COUNT(поле), при работе, как и было сказано, игнорирует значение NULL, а COUNT(*) просто подсчитывает общее число строк. Ей все равно есть там NULL или нет! 🙂 Как в том мультике — «Он нас всех посчитал!» 🙂 Просто запомните вышесказанное и не будете делать в дальнейшем ошибок! А, вот функции MIN(), MAX() — особо не искажают результат при наличии NULL, так как так же его игнорируют. Но AVG(), SUM() — может при наличии NULL немного ввести вас в заблуждение! Например, посмотрите на следующий запрос:

SELECT SUM(SALES), SUM(QUOTA), (SUM(SALES) - SUM(QUOTA)), (SUM(SALES - QUOTA)) FROM SALESREPS /

SQL> SELECT SUM(SALES), SUM(QUOTA), (SUM(SALES) - SUM(QUOTA)), (SUM(SALES - QUOTA)) 2 FROM SALESREPS 3 / SUM(SALES) SUM(QUOTA) (SUM(SALES)-SUM(QUOTA)) (SUM(SALES-QUOTA)) ---------- ---------- ----------------------- ------------------ 3279,574 3100 179,574 103,589

1. Если какие либо из значений содержащихся в столбце, равны NULL, при вычислении результата функции они исключаются!
2. Если все значения в столбце равны NULL, то функции AVG(), SUM(), MIN(), MAX() возвращают значения NULL! Функция COUNT() возвращает ноль!
3. Если в столбце нет значений (т.е. столбец пуст), то функции AVG(), SUM(), MIN(), MAX() возвращают значения NULL! Функция COUNT() возвращает ноль!
4. Функция COUNT(*) подсчитывает количество строк и не зависит от наличия или отсутствия в столбце значений NULL! Если строк в столбце нет, то эта функция возвращает ноль!

Вот собственно и все вкратце, что касается NULL и агрегатных функций! Можете проверить все сами! 🙂 Еще один интересный момент, касающийся функции DISTINCT. Ее тоже можно использовать с агрегатными функциями. Например в таких запросах:

1. Сколько различных названий рапортов существует в нашей компании?

SELECT COUNT(DISTINCT TITLE) FROM SALESREPS /

SQL> SELECT COUNT(DISTINCT TITLE) 2 FROM SALESREPS 3 / COUNT(DISTINCTTITLE) -------------------- 5

2. В скольких офисах есть служащие превысившие плановые объемы продаж?

SELECT COUNT(DISTINCT REP_OFFICE) FROM SALESREPS WHERE SALES > QUOTA /

SQL> SELECT COUNT(DISTINCT REP_OFFICE) 2 FROM SALESREPS 3 WHERE SALES > QUOTA 4 / COUNT(DISTINCTREP_OFFICE) ------------------------- 4

Вкратце опишу основные понятия, при работе с DISTINCT и агрегатами. Если вы используете DISTINCT и агрегатную функцию, то ее аргументом может быть только имя столбца, выражение не может быть аргументом. В функциях MIN(), MAX() так же нет смысла использовать DISTINCT! В функции COUNT() в принципе можно использовать DISTINCT, но это требуется не часто. А вот к функции COUNT(*) вообще не применимо DISTINCT, так как она просто подсчитывает число строк! Так же в одном запросе DISTINCT можно употреблять только один раз! Если оно применяется с аргументом, агрегатной функции, его уже нельзя использовать ни с одним другим аргументом! Вот, такие правила для DISTINCT с агрегатами, поэкспериментируйте сами и сможете убедиться!