Контакты
Главная / Интернет

Агрегатные функции SQL - SUM, MIN, MAX, AVG, COUNT. Выборка и подсчет строк одним запросом - SQL_CALC_FOUND_ROWS Предложение GROUP BY

Как узнать количество моделей ПК, выпускаемых тем или иным поставщиком? Как определить среднее значение цены на компьютеры, имеющие одинаковые технические характеристики? На эти и многие другие вопросы, связанные с некоторой статистической информацией, можно получить ответы при помощи итоговых (агрегатных) функций . Стандартом предусмотрены следующие агрегатные функции:

Все эти функции возвращают единственное значение. При этом функции COUNT, MIN и MAX применимы к любым типам данных, в то время как SUM и AVG используются только для числовых полей. Разница между функцией COUNT(*) и COUNT(<имя поля>) состоит в том, что вторая при подсчете не учитывает NULL-значения.

Пример. Найти минимальную и максимальную цену на персональные компьютеры:

Пример. Найти имеющееся в наличии количество компьютеров, выпущенных производителем А:

Пример. Если же нас интересует количество различных моделей, выпускаемых производителем А, то запрос можно сформулировать следующим образом (пользуясь тем фактом, что в таблице Product каждая модель записывается один раз):

Пример. Найти количество имеющихся различных моделей, выпускаемых производителем А. Запрос похож на предыдущий, в котором требовалось определить общее число моделей, выпускаемых производителем А. Здесь же требуется найти число различных моделей в таблице PC (т.е. имеющихся в продаже).

Для того, чтобы при получении статистических показателей использовались только уникальные значения, при аргументе агрегатных функций можно использовать параметр DISTINCT . Другой параметр ALL используется по умолчанию и предполагает подсчет всех возвращаемых значений в столбце. Оператор,

Если же нам требуется получить количество моделей ПК, производимых каждым производителем, то потребуется использовать предложение GROUP BY , синтаксически следующего после предложения WHERE .

Предложение GROUP BY

Предложение GROUP BY используется для определения групп выходных строк, к которым могут применяться агрегатные функции (COUNT, MIN, MAX, AVG и SUM) . Если это предложение отсутствует, и используются агрегатные функции, то все столбцы с именами, упомянутыми в SELECT , должны быть включены в агрегатные функции , и эти функции будут применяться ко всему набору строк, которые удовлетворяют предикату запроса. В противном случае все столбцы списка SELECT, не вошедшие в агрегатные функции, должны быть указаны в предложении GROUP BY . В результате чего все выходные строки запроса разбиваются на группы, характеризуемые одинаковыми комбинациями значений в этих столбцах. После этого к каждой группе будут применены агрегатные функции. Следует иметь в виду, что для GROUP BY все значения NULL трактуются как равные, т.е. при группировке по полю, содержащему NULL-значения, все такие строки попадут в одну группу.
Если при наличии предложения GROUP BY , в предложении SELECT отсутствуют агрегатные функции , то запрос просто вернет по одной строке из каждой группы. Эту возможность, наряду с ключевым словом DISTINCT, можно использовать для исключения дубликатов строк в результирующем наборе.
Рассмотрим простой пример:
SELECT model, COUNT(model) AS Qty_model, AVG(price) AS Avg_price
FROM PC
GROUP BY model;

В этом запросе для каждой модели ПК определяется их количество и средняя стоимость. Все строки с одинаковыми значениями model (номер модели) образуют группу, и на выходе SELECT вычисляются количество значений и средние значения цены для каждой группы. Результатом выполнения запроса будет следующая таблица:
model Qty_model Avg_price
1121 3 850.0
1232 4 425.0
1233 3 843.33333333333337
1260 1 350.0

Если бы в SELECT присутствовал столбец с датой, то можно было бы вычислять эти показатели для каждой конкретной даты. Для этого нужно добавить дату в качестве группирующего столбца, и тогда агрегатные функции вычислялись бы для каждой комбинации значений (модель−дата).

Существует несколько определенных правил выполнения агрегатных функций :

  • Если в результате выполнения запроса не получено ни одной строки (или не одной строки для данной группы), то исходные данные для вычисления любой из агрегатных функций отсутствуют. В этом случае результатом выполнения функций COUNT будет нуль, а результатом всех других функций - NULL.
  • Аргумент агрегатной функции не может сам содержать агрегатные функции (функция от функции). Т.е. в одном запросе нельзя, скажем, получить максимум средних значений.
  • Результат выполнения функции COUNT есть целое число (INTEGER). Другие агрегатные функции наследуют типы данных обрабатываемых значений.
  • Если при выполнении функции SUM был получен результат, превышающий максимальное значение используемого типа данных, возникает ошибка .

Итак, если запрос не содержит предложения GROUP BY , то агрегатные функции , включенные в предложение SELECT , исполняются над всеми результирующими строками запроса. Если запрос содержит предложение GROUP BY , каждый набор строк, который имеет одинаковые значения столбца или группы столбцов, заданных в предложении GROUP BY , составляет группу, и агрегатные функции выполняются для каждой группы отдельно.

Предложение HAVING

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

Описывается использование арифметических операторов и построение вычисляемых столбцов. Рассматриваются итоговые (агрегатные) функции COUNT, SUM, AVG, MAX, MIN. Дается пример использования оператора GROUP BY для группировки в запросах выборки данных. Описывается применение предложения HAVING.

Построение вычисляемых полей

В общем случае для создания вычисляемого (производного) поля в списке SELECT следует указать некоторое выражение языка SQL. В этих выражениях применяются арифметические операции сложения, вычитания, умножения и деления, а также встроенные функции языка SQL. Можно указать имя любого столбца (поля) таблицы или запроса, но использовать имя столбца только той таблицы или запроса, которые указаны в списке предложения FROM соответствующей инструкции. При построении сложных выражений могут понадобиться скобки.

Стандарты SQL позволяют явным образом задавать имена столбцов результирующей таблицы, для чего применяется фраза AS .

SELECT Товар.Название, Товар.Цена, Сделка.Количество, Товар.Цена*Сделка.Количество AS Стоимость FROM Товар INNER JOIN Сделка ON Товар.КодТовара=Сделка.КодТовара Пример 6.1. Рассчет общей стоимости для каждой сделки.

Пример 6.2. Получить список фирм с указанием фамилии и инициалов клиентов.

SELECT Фирма, Фамилия+""+ Left(Имя,1)+"."+Left(Отчество,1)+"."AS ФИО FROM Клиент Пример 6.2. Получение списка фирм с указанием фамилии и инициалов клиентов.

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

Пример 6.3. Получить список товаров с указанием года и месяца продажи.

SELECT Товар.Название, Year(Сделка.Дата) AS Год, Month(Сделка.Дата) AS Месяц FROM Товар INNER JOIN Сделка ON Товар.КодТовара=Сделка.КодТовара Пример 6.3. Получение списка товаров с указанием года и месяца продажи.

В запросе использованы встроенные функции Year и Month для выделения года и месяца из даты.

Использование итоговых функций

С помощью итоговых (агрегатных) функций в рамках SQL-запроса можно получить ряд обобщающих статистических сведений о множестве отобранных значений выходного набора.

Пользователю доступны следующие основные итоговые функции :

  • Count (Выражение) - определяет количество записей в выходном наборе SQL-запроса;
  • Min/Max (Выражение) - определяют наименьшее и наибольшее из множества значений в некотором поле запроса;
  • Avg (Выражение) - эта функция позволяет рассчитать среднее значение множества значений, хранящихся в определенном поле отобранных запросом записей. Оно является арифметическим средним значением, т.е. суммой значений, деленной на их количество.
  • Sum (Выражение) - вычисляет сумму множества значений, содержащихся в определенном поле отобранных запросом записей.

Чаще всего в качестве выражения выступают имена столбцов. Выражение может вычисляться и по значениям нескольких таблиц.

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

Если до применения обобщающей функции необходимо исключить дублирующиеся значения, следует перед именем столбца в определении функции поместить ключевое слово DISTINCT . Оно не имеет смысла для функций MIN и MAX , однако его использование может повлиять на результаты выполнения функций SUM и AVG , поэтому необходимо заранее обдумать, должно ли оно присутствовать в каждом конкретном случае. Кроме того, ключевое слово DISTINCT может быть указано в любом запросе не более одного раза.

Очень важно отметить, что итоговые функции могут использоваться только в списке предложения SELECT и в составе предложения HAVING . Во всех других случаях это недопустимо. Если список в предложении SELECT содержит итоговые функции , а в тексте запроса отсутствует фраза GROUP BY , обеспечивающая объединение данных в группы, то ни один из элементов списка предложения SELECT не может включать каких-либо ссылок на поля, за исключением ситуации, когда поля выступают в качестве аргументов итоговых функций .

Пример 6.4. Определить первое по алфавиту название товара.

SELECT Min(Товар.Название) AS Min_Название FROM Товар Пример 6.4. Определение первого по алфавиту названия товара.

Пример 6.5. Определить количество сделок.

SELECT Count(*) AS Количество_сделок FROM Сделка Пример 6.5. Определить количество сделок.

Пример 6.6. Определить суммарное количество проданного товара.

SELECT Sum(Сделка.Количество) AS Количество_товара FROM Сделка Пример 6.6. Определение суммарного количества проданного товара.

Пример 6.7. Определить среднюю цену проданного товара.

SELECT Avg(Товар.Цена) AS Avg_Цена FROM Товар INNER JOIN Сделка ON Товар.КодТовара=Сделка.КодТовара; Пример 6.7. Определение средней цены проданного товара.

SELECT Sum(Товар.Цена*Сделка.Количество) AS Стоимость FROM Товар INNER JOIN Сделка ON Товар.КодТовара=Сделка.КодТовара Пример 6.8. Подсчет общей стоимости проданных товаров.

Предложение GROUP BY

Часто в запросах требуется формировать промежуточные итоги, что обычно отображается появлением в запросе фразы "для каждого...". Для этой цели в операторе SELECT используется предложение GROUP BY . Запрос, в котором присутствует GROUP BY , называется группирующим запросом, поскольку в нем группируются данные, полученные в результате выполнения операции SELECT , после чего для каждой отдельной группы создается единственная суммарная строка. Стандарт SQL требует, чтобы предложение SELECT и фраза GROUP BY были тесно связаны между собой. При наличии в операторе SELECT фразы GROUP BY каждый элемент списка в предложении SELECT должен иметь единственное значение для всей группы. Более того, предложение SELECT может включать только следующие типы элементов: имена полей, итоговые функции , константы и выражения, включающие комбинации перечисленных выше элементов.

Все имена полей, приведенные в списке предложения SELECT , должны присутствовать и во фразе GROUP BY - за исключением случаев, когда имя столбца используется в итоговой функции . Обратное правило не является справедливым - во фразе GROUP BY могут быть имена столбцов, отсутствующие в списке предложения SELECT .

Если совместно с GROUP BY используется предложение WHERE , то оно обрабатывается первым, а группированию подвергаются только те строки, которые удовлетворяют условию поиска.

Стандартом SQL определено, что при проведении группирования все отсутствующие значения рассматриваются как равные. Если две строки таблицы в одном и том же группируемом столбце содержат значение NULL и идентичные значения во всех остальных непустых группируемых столбцах, они помещаются в одну и ту же группу.

Пример 6.9. Вычислить средний объем покупок, совершенных каждым покупателем.

SELECT Клиент.Фамилия, Avg(Сделка.Количество) AS Среднее_количество FROM Клиент INNER JOIN Сделка ON Клиент.КодКлиента=Сделка.КодКлиента GROUP BY Клиент.Фамилия Пример 6.9. Вычисление среднего объема покупок, совершенных каждым покупателем.

Фраза "каждым покупателем" нашла свое отражение в SQL-запросе в виде предложения GROUP BY Клиент.Фамилия .

Пример 6.10. Определить, на какую сумму был продан товар каждого наименования.

SELECT Товар.Название, Sum(Товар.Цена*Сделка.Количество) AS Стоимость FROM Товар INNER JOIN Сделка ON Товар.КодТовара=Сделка.КодТовара GROUP BY Товар.Название Пример 6.10. Определение, на какую сумму был продан товар каждого наименования.

SELECT Клиент.Фирма, Count(Сделка.КодСделки) AS Количество_сделок FROM Клиент INNER JOIN Сделка ON Клиент.КодКлиента=Сделка.КодКлиента GROUP BY Клиент.Фирма Пример 6.11. Подсчет количества сделок, осуществленных каждой фирмой.

SELECT Клиент.Фирма, Sum(Сделка.Количество) AS Общее_Количество, Sum(Товар.Цена*Сделка.Количество) AS Стоимость FROM Товар INNER JOIN (Клиент INNER JOIN Сделка ON Клиент.КодКлиента=Сделка.КодКлиента) ON Товар.КодТовара=Сделка.КодТовара GROUP BY Клиент.Фирма Пример 6.12. Подсчет общего количества купленного для каждой фирмы товара и его стоимости.

Пример 6.13. Определить суммарную стоимость каждого товара за каждый месяц.

SELECT Товар.Название, Month(Сделка.Дата) AS Месяц, Sum(Товар.Цена*Сделка.Количество) AS Стоимость FROM Товар INNER JOIN Сделка ON Товар.КодТовара=Сделка.КодТовара GROUP BY Товар.Название, Month(Сделка.Дата) Пример 6.13. Определение суммарной стоимости каждого товара за каждый месяц.

Пример 6.14. Определить суммарную стоимость каждого товара первого сорта за каждый месяц.

SELECT Товар.Название, Month(Сделка.Дата) AS Месяц, Sum(Товар.Цена*Сделка.Количество) AS Стоимость FROM Товар INNER JOIN Сделка ON Товар.КодТовара=Сделка.КодТовара WHERE Товар.Сорт="Первый" GROUP BY Товар.Название, Month(Сделка.Дата) Пример 6.14. Определение суммарной стоимости каждого товара первого сорта за каждый месяц.

Предложение HAVING

При помощи HAVING отражаются все предварительно сгруппированные посредством GROUP BY блоки данных, удовлетворяющие заданным в HAVING условиям. Это дополнительная возможность "профильтровать" выходной набор.

Условия в HAVING отличаются от условий в WHERE :

  • HAVING исключает из результирующего набора данных группы с результатами агрегированных значений;
  • WHERE исключает из расчета агрегатных значений по группировке записи, не удовлетворяющие условию;
  • в условии поиска WHERE нельзя задавать агрегатные функции.

Пример 6.15. Определить фирмы, у которых общее количество сделок превысило три.

SELECT Клиент.Фирма, Count(Сделка.Количество) AS Количество_сделок FROM Клиент INNER JOIN Сделка ON Клиент.КодКлиента=Сделка.КодКлиента GROUP BY Клиент.Фирма HAVING Count(Сделка.Количество)>3 Пример 6.15. Определение фирм, у которых общее количество сделок превысило три.

Пример 6.16. Вывести список товаров, проданных на сумму более 10000 руб.

SELECT Товар.Название, Sum(Товар.Цена*Сделка.Количество) AS Стоимость FROM Товар INNER JOIN Сделка ON Товар.КодТовара=Сделка.КодТовара GROUP BY Товар.Название HAVING Sum(Товар.Цена*Сделка.Количество)>10000 Пример 6.16. Вывод списка товаров, проданных на сумму более 10000 руб.

Пример 6.17. Вывести список товаров, проданных на сумму более 10000 без указания суммы.

SELECT Товар.Название FROM Товар INNER JOIN Сделка ON Товар.КодТовара=Сделка.КодТовара GROUP BY Товар.Название HAVING Sum(Товар.Цена*Сделка.Количество)>10000 Пример 6.17. Вывод списка товаров, проданных на сумму более 10000 без указания суммы.

есть запрос вида:

SELECT i.*, COUNT(*) AS currencies, SUM(ig.quantity) AS total, SUM(g.price * ig.quantity) AS price, c.briefly AS cname FROM invoice AS i, invoice_goods AS ig, good g LEFT JOIN currency c ON (c.id = g.currency) WHERE ig.invoice_id = i.id AND g.id = ig.good_id GROUP BY g.currency ORDER BY i.date DESC;

т.е. выбирается список заказов, в котором считаются суммарные стоимости товаров в разных валютах (валюта установлена у товара, столбец cname в результате — название валюты)

нужно в столбце результата currencies получать количество записей с одинаковым i.id , однако, эксперименты с параметрами COUNT() ни к чему не привели — всегда возвращает 1

Вопрос: возможно ли получить истинное значение в столбце currencies ? Т.е. если заказаны товары с ценами в 3х разных валютах, currencies=3 ?

Слишком большие вольности допускает MySQL по отношению к SQL однако. Что например значит i.* в контексте этого селекта? Все колонки таблицы invoice? Так как к ним не применяется никакая групповая функция, то неплохо было бы чтобы они были перечислены в GROUP BY, иначе не совсем ясен принцип группировки строк. Если необходимо получить все товары по всем заказам в разрезе валют, это одно, если необходимо получить все товары сгруппированные по валютам в разрезе каждого заказа, это совсем другое.
Исходя из вашего селекта можно предположить следующую структуру данных:
Таблица invoice:

Таблица invoice_goods:

Таблица goods:

Таблица currency:

Что вернёт ваш текущий селект? По идее он вернёт для каждого заказа N-строк по каждой валюте в которой в этом заказе есть товары. Но из-за того что в group by не указано ничего кроме g.currency, это не очевидно:), более того, колонка c.briefly тоже вносит свою лепту в неявное образование групп. Что же мы имеем в итоге, для каждой уникального сочетания i.*, g.currency и c.briefly будет сформирована группа к строчкам которой будут применены функции SUM и COUNT. То что в результате игры с параметром COUNT у вас выходила всегда 1 означает что в результирующей группе была только одна запись (т.е. группы формируются не так как вам, возможно, требуется, можете описать требования поподробнее?). Из вашего вопроса не ясно, что вы хотели бы узнать - сколько разных валют учавствовало в заказе или сколько заказов было в данной валюте? В первом случае возможны несколько вариантов всё зависит от возможностей mySQL, во втором надо по-другому написать селект выражение.

Слишком большие вольности допускает MySQL по отношению к SQL однако. Что например значит i.* в контексте этого селекта? Все колонки таблицы invoice?

Да, именно. Но большой роли это не играет, т.к. полезных в данном случае среди них (колонок) нет. Пускай i.* будет i.id . Для определённости.

Что вернёт ваш текущий селект? По идее он вернёт для каждого заказа N-строк по каждой валюте в которой в этом заказе есть товары. Но из-за того что в group by не указано ничего кроме g.currency, это не очевидно:),

Именно так.
Вернёт он следующее (в этом примере из i я выбираю только id , а не все столбцы):

id currencies total price cname
33 1 1.00 198.00 BF
33 1 4.00 1548.04 РУБ
более того, колонка c.briefly тоже вносит свою лепту в неявное образование групп.

Каким образом? По c.id=g.currency делается соединение таблиц, а группировка по g.currency .

То что в результате игры с параметром COUNT у вас выходила всегда 1 означает что в результирующей группе была только одна запись

Нет, группа строилась из 1й записи. Насколько я это понял, COUNT() возвращает 1 именно поэтому (ведь столбцы, которые в группе отличаются (правда, кроме столбца валют), создаются аггрегатными функциями).

(т.е. группы формируются не так как вам, возможно, требуется, можете описать требования поподробнее?).

Группы формируются так как надо, каждая группа -- это сумарная стоимость товаров в каждой валюте . Однако, мне, помимо того, нужно посчитать, сколько же элементов в этой группе .

Из вашего вопроса не ясно, что вы хотели бы узнать - сколько разных валют учавствовало в заказе или сколько заказов было в данной валюте?

Да уж, заработался немного. Как раз первое.

dmig[досье]
Под "неявным" участием в образованием группы я подразумеваю, что если колонка не указана в GROUP BY, и при этом НЕ является аргументом групповой функции, то результат селекта будет идентичен тому, как если бы эта колонока БЫЛА указана в GROUP BY. Ваш селект, и селект приведённый ниже выведут абсолютно одинаковый результат (не обращайте внимания на join"ы, я просто привёл их к единому формату записи):

Select i.id id, count(*) currencies, sum(ig.quantity) total, SUM(g.price * ig.quantity) price, c.briefly cname FROM invoice i join invoice_goods ig on (ig.invoice_id = i.id) join good g on (g.id = ig.good_id) LEFT OUTER JOIN currency c ON (c.id = g.currency) group by i.id, c.briefly

Получается что в каждой строке результирующей выборки есть одна, и только одна валюта (если бы это было по другому, то и строк было бы две). О количестве каких элементов в таком случае речь? О пунктах заказа? Тогда ваш селект абсолютно правильный, просто по данной валюте, в данном заказе только один пункт.
Давайте рассмотрим схему данных:

  1. В одном заказе множество пунктов (строк),так?
  2. Каждый пункт это товар в справочнике goods, так?
  3. Каждый товар имеет определённую (и только одну) валюту, это следует из c.id = g.currency, так?

Сколько валют в заказе? Столько сколько в нём пунктов с РАЗНЫМИ валютами.
Складывать g.price * ig.quantity имеет смысл только для пунктов в одной валюте;) (хотя километры с часами, тоже складывать можно:) Так что же вас не устраивает!? Вы утверждаете, что вам надо сколько разных валют учавствовало в заказе
и в таком случае сделать это в рамках этого же селекта без всяческих ухищрений (которые скорее всего не потянет MySQL) не получится;(
Я к сожалению не знаток MySQL. В oracle сделать это одним селектом можно, но поможет ли вам такой совет? Вряд ли;)

# В одном заказе множество пунктов (строк),так?
# Каждый пункт это товар в справочнике goods, так?
# Каждый товар имеет определённую (и только одну) валюту, это следует из c.id = g.currency, так?

Так.
Один заказ: одна запись в таблице invoice, ей соответствуют n(>0) записей в invoice_goods, каждой из которых соответствует 1 запись в таблице goods, запись "валюта" в каждой из которых, в свою очередь, соответствует 1й записи в таблице currency (LEFT JOIN — на случай редактирования справочника валют кривыми руками — таблицы типа MyISAM не поддерживают внешних ключей).

Сколько валют в заказе? Столько сколько в нём пунктов с РАЗНЫМИ валютами.

Да, именно так.

Складывать g.price * ig.quantity имеет смысл только для пунктов в одной валюте;) (хотя километры с часами, тоже складывать можно:)

Именно поэтому делается группировка по id валюты (g.currency).

В oracle сделать это одним селектом можно, но поможет ли вам такой совет?

М.б.
Я немного общался с Oracle, с pl/sql знаком.

Вариант №1.

Select a.*, count(*) over (partition by a.id) currencies from (select i.id id, sum(ig.quantity) total, SUM(g.price * ig.quantity) price, c.briefly cname FROM invoice i join invoice_goods ig on (ig.invoice_id = i.id) join good g on (g.id = ig.good_id) LEFT OUTER JOIN currency c ON (c.id = g.currency) group by i.id, c.briefly) a

Это использует т.н. analytic function. С вероятностью 99% НЕ работает в MySQL.

Вариант №2.
Создаётся функция, countCurrencies например, которая по id заказа возвращает кол-во валют которое в нём учавствовало и тогда:

Select i.id id, countCurrencies(i.id) currencies, sum(ig.quantity) total, SUM(g.price * ig.quantity) price, c.briefly cname FROM invoice i join invoice_goods ig on (ig.invoice_id = i.id) join good g on (g.id = ig.good_id) LEFT OUTER JOIN currency c ON (c.id = g.currency) group by i.id, c.briefly, countCurrencies(i.id)

Может прокатить... но будет вызываться для каждой валюты каждого заказа. Не знаю даёт ли MySQL делать GROUP BY по функции...

Вариант №3

Select i.id id, agr.cnt currencies, sum(ig.quantity) total, SUM(g.price * ig.quantity) price, c.briefly cname FROM invoice i join invoice_goods ig on (ig.invoice_id = i.id) join good g on (g.id = ig.good_id) LEFT OUTER JOIN currency c ON (c.id = g.currency) left outer join (select ii.id, count(distinct gg.currency) cnt from invoice ii, invoce_goods iig, good gg where ii.id = iig.invoice_id and gg.id = iig.good_id group by ii.id) agr on (i.id = agr.id) group by i.id, c.briefly, agr.cnt

Наверное самый правильный... и вполне возможно самый рабочий вариант из всех.

Самый быстрый это Вариант №1. №2 самый неэффективный, т.к. чем больше валют в заказе, тем чаще они считаются.
№3 тоже в принципе не лучший по скорости, но по крайней мере можно положится на кэширование внутри СУБД.

результатом всех трёх селектов будет следующее:

id currencies total price cname
33 2 1.00 198.00 BF
33 2 4.00 1548.04 РУБ

для одного и того же id цифра в колонке currencies будет всегда одинаковая, это то что вам надо?

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

Описание

В SQL Server (Transact-SQL) функция COUNT возвращает количество строк поля или выражения в результирующий набор.

Синтаксис

Синтаксис функции COUNT в SQL Server (Transact-SQL):

ИЛИ синтаксис функции COUNT при группировке результатов одного или нескольких столбцов:

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

expression1 , expression2 , … expression_n
Выражения, которые не заключены в функцию COUNT и должны быть включены в предложение GROUP BY в конце SQL предложения.
aggregate_expression — это столбец или выражение, чьи не NULL значения будут подсчитаны.
tables — таблицы, из которых вы хотите получить записи. Должна быть хотя бы одна таблица, перечисленная в операторе FROM.
WHERE conditions — необязательный. Это условия, которые должны выполняться для выбранных записей.

Включение не NULL значений

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

Рассмотрим пример функции COUNT, который демонстрирует, как значения NULL оцениваются функцией COUNT.

Например, если у вас есть следующая таблица, называемая markets :

Этот пример COUNT вернет 3, так как все значения market_id в наборе результатов запроса НЕ являются NULL.

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

Transact-SQL

SELECT COUNT(filials) FROM markets; --Результат: 1

Этот пример COUNT будет возвращать только 1, так как только одно значение filials в наборе результатов запроса НЕ NULL. Это будет первая строка, где будет указано filials = «yes». Это единственная строка, которая включена в вычисление функции COUNT.

Применение

Функция COUNT может использоваться в следующих версиях SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

Пример с одним полем

Рассмотрим некоторые примеры SQL Server функции COUNT, чтобы понять, как использовать функцию COUNT в SQL Server (Transact-SQL).

Например, вы можете узнать, сколько contacts имеет пользователь с last_name = «Rasputin».

В этом примере функции COUNT мы выражению COUNT (*) указали алиас «Number of contacts». Поэтому в результирующем наборе будет отображаться «Number of contacts» в качестве имени поля.

Пример с использованием DISTINCT

Вы можете использовать оператор DISTINCT в функции COUNT. Например, приведенный ниже оператор SQL возвращает число уникальных department , где хотя бы один сотрудник имеет first_name = ‘Samvel’.

SQL - Урок 8. Группировка записей и функция COUNT()

Давайте вспомним, какие сообщения и в каких темах у нас имеются. Для этого можно воспользоваться привычным запросом:

А что, если нам надо лишь узнать сколько сообщений на форуме имеется. Для этого можно воспользоваться встроенной функцией COUNT() . Эта функция подсчитывает число строк. Причем, если в качестве аргумента этой функции выступает *, то подсчитываются все строки таблицы. А если в качестве аргумента указывается имя столбца, то подсчитываются только те строки, которые имеют значение в указанном столбце.

В нашем примере оба аргумента дадут одинаковый результат, т.к. все столбцы таблицы имеют тип NOT NULL. Давайте напишем запрос, используя в качестве аргумента столбец id_topic:

SELECT COUNT(id_topic) FROM posts;

Итак, в наших темах имеется 4 сообщения. Но что, если мы хотим узнать сколько сообщений имеется в каждой теме. Для этого нам понадобится сгруппировать наши сообщения по темам и вычислить для каждой группы количество сообщений. Для группировки в SQL используется оператор GROUP BY . Наш запрос теперь будет выглядеть так:

SELECT id_topic, COUNT(id_topic) FROM posts GROUP BY id_topic;

Оператор GROUP BY указывает СУБД сгруппировать данные по столбцу id_topic (т.е. каждая тема - отдельная группа) и для каждой группы подсчитать количество строк:

Ну вот, в теме с id=1 у нас 3 сообщения, а с id=4 - одно. Кстати, если бы в поле id_topic были возможны отсутствия значений, то такие строки были бы объединены в отдельную группу со значением NULL.

Предположим, что нас интересуют только те группы, в которых больше двух сообщений. В обычном запросе мы указали бы условие с помощью оператора WHERE , но этот оператор умеет работать только со строками, а для групп те же функции выполняет оператор HAVING :

SELECT id_topic, COUNT(id_topic) FROM posts GROUP BY id_topic HAVING COUNT(id_topic) > 2;

В результате имеем:

В уроке 4 мы рассматривали, какие условия можно задавать оператором WHERE , те же условия можно задавать и оператором HAVING , только надо запомнить, что WHERE фильтрует строки, а HAVING - группы.

Итак, сегодня мы узнали, как создавать группы и как подсчитать количество строк в таблице и в группах. Вообще вместе с оператором GROUP BY можно использовать и другие встроенные функции, но их мы будем изучать позже.