В этой статье описано, как подсчитать данные, возвращаемые запросом. Например, в форме или отчете можно подсчитать количество элементов в одном или нескольких полях таблицы или элементах управления. Вы также можете вычислять средние значения, находить наибольшее и наименьшее значения, самую давнюю и самую последнюю дату. Кроме того, в Access предусмотрено средство, называемое строкой итогов, с помощью которого можно подсчитывать данные в таблице, не изменяя структуру запроса.
Выберите нужное действие
Способы подсчета данных
Подсчитать количество элементов в поле (столбце значений) можно с помощью функции Число . Функция Число принадлежит к ряду функций, называемых агрегатными. Агрегатные функции выполняют вычисления со столбцами данных и возвращают единственное значение. Кроме функции Число , в Access есть следующие агрегатные функции:
Сумма для суммирования столбцов чисел;
Среднее для вычисления среднего значения в столбце чисел;
Максимум для нахождения наибольшего значения в поле;
Минимум для нахождения наименьшего значения в поле;
Стандартное отклонение для оценки разброса значений относительно среднего значения;
Дисперсия для вычисления статистической дисперсии всех значений в столбце.
В Access предусмотрено два способа добавления функции Count и других агрегатных функций в запрос. Вы можете:
Открыть запрос в режиме таблицы и добавить строку итогов. Строка итогов позволяет использовать агрегатные функции в одном или нескольких столбцах в результатах запроса без необходимости изменять его структуру.
Создать итоговый запрос. В итоговом запросе вычисляются промежуточные итоги по группам записей. Например, если вы хотите вычислить промежуточную сумму всех продаж по городам или по кварталам, следует использовать итоговый запрос для группировки записей по нужной категории, а затем просуммировать все объемы продаж. С другой стороны, с помощью строки итогов можно вычислить общий итог для одного или нескольких столбцов (полей) данных.
Примечание: Ниже в разделах этой статьи подробно описано применение функции Сумма , однако следует помнить, что вы можете использовать другие агрегатные функции в строках итогов и запросах. Дополнительные сведения об использовании других агрегатных функций см. ниже в разделе .
Дополнительные сведения о способах использования других агрегатных функций см. в разделе .
В следующих разделах описаны шаги, которые следует выполнить, чтобы добавить строку итогов, а также описывается использование итогового запроса для подсчета данных. Следует обратить внимание на то, что функция Число работает с большим числом типов данных, чем другие агрегатные функции. Функцию Число можно использовать для любого типа полей, кроме тех, которые содержат сложные повторяющиеся скалярные данные, например поле с многозначными списками.
С другой стороны, многие агрегатные функции работают только с данными в полях, имеющих определенный тип данных. Например, функция Сумма работает только с типами данных "Число", "Действительное" и "Денежный". Дополнительные сведения о типах данных, требуемых для каждой функции, см. ниже в разделе .
Общие сведения о типах данных см. в статье Изменение типа данных для поля .
Подсчет данных с помощью строки итогов
Чтобы добавить в запрос строку итогов, откройте его в режиме таблицы, добавьте строку, а затем выберите функцию Число или другую агрегатную функцию, например Сумма , Минимум , Максимум или Среднее . В этом разделе объясняется, как создать простой запрос на выборку и добавить строку итогов.
Создание простого запроса на выборку
Добавление строки итогов
Скрытие строки итогов
На вкладке Главная в группе Записи нажмите кнопку Итоги .
Дополнительные сведения об использовании строки итогов см. в разделе Отображение итогов по столбцу в таблице .
Подсчет данных с помощью итогового запроса
Когда нужно подсчитать некоторые или все записи, возвращаемые запросом, то вместо строки итогов можно воспользоваться итоговым запросом. Например, вы можете подсчитать общее число сделок или число сделок в отдельном городе.
Как правило, итоговый запрос применяется вместо строки итогов тогда, когда требуется использовать значение результата в другой части базы данных, например в отчете.
Подсчет всех записей в запросе
Подсчет записей в группе или категории
Справочные сведения об агрегатных функциях
В следующей таблице перечислены и отписаны агрегатные функции Access, которые можно использовать в строке итогов и в запросах. Помните, что в Access предусмотрено больше агрегатных функций для запросов, чем для строки итогов. Кроме того, при работе с проектом Access (внешней базой данных Access, которая подключается к базе данных Microsoft SQL Server) можно использовать расширенный набор агрегатных функций, предоставляемый SQL Server. Дополнительные сведения о них см. в электронной документации Microsoft SQL Server.
|
Функция |
Описание |
Поддерживаемые типы данных |
|
Сумма |
Суммирует элементы в столбце. Подходит только для числовых и денежных данных. |
|
|
Среднее |
Вычисляет среднее значение для столбца. Столбец должен содержать числовые или денежные величины или значения даты или времени. Функция игнорирует пустые значения. |
|
|
Число |
Подсчитывает число элементов в столбце. |
Все типы данных, за исключением сложных повторяющихся скалярных данных, таких как столбец многозначных списков. Дополнительные сведения о многозначных списках можно найти в статье рекомендации по созданию и удалению многозначных полей в руководствах , посвященных многозначным полям. |
|
Максимум |
Возвращает элемент, имеющий наибольшее значение. Для текстовых данных наибольшим будет последнее по алфавиту значение, причем Access не учитывает регистр. Функция игнорирует пустые значения. |
"Число", "Действительное", "Денежный", "Дата/время" |
|
Минимум |
Возвращает элемент, имеющий наименьшее значение. Для текстовых данных наименьшим будет первое по алфавиту значение, причем Access не учитывает регистр. Функция игнорирует пустые значения. |
"Число", "Действительное", "Денежный", "Дата/время" |
|
Стандартное отклонение |
Показывает, насколько значения отклоняются от среднего. Дополнительные сведения об этой функции см. в статье Отображение итогов по столбцу в таблице . |
"Число", "Действительное", "Денежный" |
|
Дисперсия |
Вычисляет статистическую дисперсию для всех значений в столбце. Подходит только для числовых и денежных данных. Если таблица содержит менее двух строк, Access возвращает пустое значение. Дополнительные сведения о функциях для расчета дисперсии см. в разделе Отображение итогов по столбцу в таблице . |
"Число", "Действительное", "Денежный" |
Особенности функции COUNT.
| Наименование параметра | Значение |
| Тема статьи: | Особенности функции COUNT. |
| Рубрика (тематическая категория) | Торговля |
Агрегатные функции.
Итоговые запросы на чтение
Итоговые запросы на чтение позволяют получить промежуточные или окончательные итоги (статистическую информацию) по содержащимся в базе данных значениям. Итоговые запросы крайне важно использовать, когда требуемой информации в базе данных в явной виде нет, и ее крайне важно вычислить.
Для подведения итогов по информации, содержащейся в базе данных, в SQL предусмотрены агрегатные (статистические) функции.
Агрегатами называют группы строк, следовательно, агрегатными функциями называют функции, аргументами которых являются группы строк.
Агрегаты могут представлять собой все строки таблицы или группы строк, созданные предложением GROUP BY (будет рассмотрено в п.13.7.2).
Агрегатная функция получает в качестве аргумента выражение, содержащее, по крайней мере, один столбец таблицы, а в качестве результата возвращает одно значение. Τᴀᴋᴎᴍ ᴏϬᴩᴀᴈᴏᴍ, агрегатные функции позволяют выполнять операции над значениями сразу целого столбца таблицы или нескольких таблиц.
В SQL имеется пять стандартных агрегатных функций: SUM, AVG, MIN, MAX, COUNT.
SUM(<выражение>) вычисляет сумму всех значений в выражении;
AVG(<выражение>) вычисляет среднее всех значений в выражении;
MIN(<выражение>) находит наименьшее среди всех значений в выражении;
MAX(<выражение>) находит наибольшее среди всех значений в выражении;
COUNT(<выражение>) подсчитывает количество значений в выражении.
В качестве выражения чаще всего выступает имя столбца таблицы.
Функция COUNT(*) подсчитывает количество строк (ᴛ.ᴇ. учитывает и NULL).
Функция COUNT(DISTINCT <выражение>) подсчитывает количество разных значений в выражении.
Примечание 1. В Microsoft Access конструкция COUNT(DISTINCT <выражение>) не работает!!! Данная проблема решается применением вложенного запроса в предложении FROM.
Ограничения на использование агрегатных функций:
Агрегатные функции нельзя использовать в предложении WHERE (оно работает для одной строки, а не для агрегата);
Агрегатные функции нельзя вкладывать друг в друга (при крайне важно сти можно использовать вложенные запросы);
В предложении SELECT нельзя одновременно использоваться агрегатные функции и обычные имена столбцов (если только по этим столбцам не указана группировка GROUP BY)
Примечание 2. В предложении SELECT возвращаемым столбцам, в которых используются агрегатные функции, рекомендуется присваивать псевдонимы.
Пример 29 . Вычислить суммарную стоимость всех вызовов.
SELECT SUM(STOIM)
FROM VYZOVY
Пример 30 . Вычислить средний возраст для контактов.
SELECT AVG(VOZRAST)
FROM KONTAKTY
Пример 31 . Вычислить минимальную и максимальную длительность исходящих вызовов.
SELECT MIN(DLIT), MAX(DLIT)
FROM VYZOVY V, TIPY_VYZ T
WHERE (V.TIP_ID=T.ID) AND (T.NAZV="Исходящий")
Пример 32 . Вывести дату и время самого первого вызова.
SELECT MIN(VREMYA)
FROM VYZOVY
Пример 33 . Вычислить количество контактов старше 30 лет.
SELECT COUNT(ID)
FROM KONTAKTY
WHERE VOZRAST>30
SELECT COUNT(*)
FROM KONTAKTY
WHERE VOZRAST>30
Пример 34 . Вычислить количество мелодий, уже назначенных для какого-либо контакта.
Не работает в ACCESS:
SELECT COUNT(DISTINCT MEL_ID)
FROM KONTAKTY
Работает в ACCESS:
SELECT COUNT(MEL_ID)
FROM (SELECT DISTINCT MEL_ID
FROM KONTAKTY)
Особенности функции COUNT. - понятие и виды. Классификация и особенности категории "Особенности функции COUNT." 2017, 2018.
Дополнительный список литературы Литература DeGolyer E., Vance H., Bibliography of the Petroleum Industry, Bull. 83, A. and. M. College of Texas, College Station, Texas, 725 p., 1944. Dunbar С. Р., Dunbar L., A Selected List of Periodicals, Serials, and Books Dealing with Petroleum and Allied Subjects, Department of Conservation, New Orleans, Louisiana, 218, p., 1939. Economic Geology Publishing Company, Urbana, Illinois,... .
Our country has always paid great attention to the education of petroleum engineers. Soon after the October Revolution in September 1918 the Soviet Government published the Decree about the organization of the Moscow Mining Academy. Later in 1930 a separate petroleum institute singled out from this Academy and we call this institute the Russian State Gubkin University of Oil and Gas. Since that time the level of petroleum education has grown manyfold. At present the system of petroleum... .
ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ» Кафедра иностранных языков УЧЕБНИК АНГЛИЙСКОГО ЯЗЫКА ПО ШИРОКОМУ ПРОФИЛЮ НЕФТЯНОГО ВУЗА Уфа 2012 Учебное пособие состоит из 16 уроков. Каждый урок включает активный словарь, лексические, грамматические,... .
The history of petroleum engineering education in our country is inseparable from the history of the foundation and development of its oil and gas industries. In tsarist Russia the petroleum industry was concentrated almost exclusively in the Caucasus. On July 2, 1918 head of the government signed the decree on the oil industry nationalization (they didn`t produce gaseous raw materials in Russia and there was no gas industry there before the revolution). But during the hard times of the Civil... .
1. Модель. 2. Требования к информации. 3. Сбор информации. 4. Активация модели и интерпретация результатов. 5. Представление результатов модели. G = Д-З Gпр = Д(В)пр – Зпр – продуктовая прибыль МД – маржинальный доход. МД = В – Зпеременые, (В – выручка, З-затраты) Модель... .
1. Do you total the accounts at the end of the fiscal year? 2. What are your usual activities at the end of the fiscal year? 3. When do you prepare a financial statement? 4. Why should you have more assets than liabilities? 5. Does the accounting department work on taxes? 6. What is the difference between your gross salary and net salary? 7. Is the difference between gross salary and net salary big in this country? What does this difference depend on? Exercise 3. Make up the... .
Подведение итогов по данным запроса В ходе этого урока мы рассмотрим те функции SQL, которые предназначены для подведения итогов. С их помощью можно осуществлять достаточно широкий спектр математических операций с данными. Основными на этом уроке будут следующие... .
Стоимость: 300 Это небольшое полезное устройство не только отображает количество рад, находящееся в человеке, но и замеряет радиационный фон окружающей среды. Бывает полезным, когда надо узнать, опасен ли громадный светящийся кратер впереди. Вес: 5 фнт... .
Как только сформирован установочный набор, начинается процесс отсчета. Отсчет отображает соотношение между ценой закрытия и максимальной или минимальной ценой два торговых дня тому назад, в зависимости от того, какой установочный набор является активным - набор на... .
Цель работы: научиться формировать на языке SQL простейшие запросы к базе данных, использовать в запросах выражения, включающие в себя арифметические операции, функции для работы со строками и датами, агрегатные функции.
Запрос на выборку всей таблицы . В лабораторной работе № 10 такой запрос уже был сформирован средствами Access. Можно упростить вид запроса, если вместо запроса
SELECT Заказы.* FROM Заказы;
написать запрос
SELECT * FROM Заказы
Язык SQL позволяет опускать имя таблицы перед именем поля в тех случаях, когда в запросе используется одна таблица, или имя поля не повторяется в нескольких таблицах в многотабличном запросе.
Создание запросов на SQL в Access начинается вызовом конструктора запросов. Для этого в окне базы данных нужно выбрать объект «Запросы», пункт меню «Создать» и в окне «Новый запрос» пункт «Конструктор». Далее выберите таблицу «Заказы» и перейдите в режим SQL. Переход в режим SQL: меню Access ВидРежим SQL.
Зокончите формирование запроса и выполните его.
Вывод избранных полей, замена имён полей псевдонимами, сортировка записей. Поля таблицы выводятся на экран дисплея в том порядке, в котором они перечислены в запросе. Имена полей при выводе результатов запроса часто неудобны для чтения. Их можно заменить в запросе псевдонимами, как показано в примере:
SELECT КодЗаказа AS Заказ, НазваниеПолучателя AS Получатель, АдресПолучателя AS Адрес, ДатаИсполнения AS Дата FROM Заказы ORDER BY НазваниеПолучателя ASC;
В примере КодЗаказа, НазваниеПолучателя, АдресПолучателя и ДатаИсполнения – имена полей в таблице «Заказы». При выводе результатов запроса на экран дисплея имена полей будут заменены соответствующими псевдонимами, указанными после словаAS .
Предложение
ORDER BY НазваниеПолучателя ASC
служит для сортировки отобранных записей по возрастанию (т.е. в алфавитном порядке) значения поля НазваниеПолучателя. Если нужно сортировать по убыванию, то вместо ASC нужно использовать DESC (сокращение от descending).
Сформируйте и выполните этот запрос.
Вывод записей без дублирования. Сформируйте и выполните следующий запрос
SELECT НазваниеПолучателя AS Получатель FROM Заказы ORDER BY НазваниеПолучателя DESC.
Названия получателей многократно повторяются, так как выбраны все записи таблицы. Чтобы не было дублирования записей, добавьте в запрос после слова SELECT слово DISTINCT. Иногда в СУБД режим DISTINCT установлен по умолчанию. Для вывода всех записей в этом случае после слова SELECT вставляется слово ALL.
Использование в запросе выражений . В списке вывода можно указывать не только имена полей и их псевдонимы, но и выражения, включающие в себя арифметические действия и функции.
Умножение. Сформируйте запрос на вывод из таблицы «Заказано» кода товара, цены, количества и общей стоимости заказанного товара. Запрос выглядит так:
SELECT КодТовара,Цена,Количество,Цена*Количество AS Стоимость FROM Заказано;
Самостоятельно дополните запрос стоимостью со скидкой.
Использование функций. ФункцияSTR() предназначена для преобразования в текстовый тип. Для вывода на экран дисплея стоимости товара в тысячах рублей с указанием единицы измерения служит следующий запрос:
SELECT КодТовара,str (Цена*Количество/1000)+" тыс. руб" AS Стоимость FROM Заказано;
Для того чтобы в колонке «Стоимость» печатались число и текст, нужно преобразовать число в текстовый тип и объединить с текстом "тыс. руб.". Для преобразования служат функция str(<выражение числового типа>) и операция слияния «+» (конкатенация).
Сформируйте запрос, в котором из таблицы «Заказы» выбираются 5 полей и результат выводится в две колонки. В первую колонку выводится поле «КодЗаказа», а в колонке с псевдонимом «Адрес клиента» объединены следующие поля: ИндексПолучателя, СтранаПолучателя, ГородПолучателя, НазваниеПолучателя.
Не забудьте поставить между объединяемыми полями адреса запятую с пробелом. Результат запроса (показаны две первые строки) должен иметь вид:
Функция выделения части даты DATEPART(). Познакомьтесь с описанием этой функции в справке Access (Содержание, раздел «Справочник по языку Visual Basic», пункт «Functions», буква D).
Определите с помощью запроса к таблице «Заказы», за какие годы были поставки товаров.
Агрегатные функции. (В Access они называются статистическими). Подсчитаем общее количество записей в таблице «Заказы» и количество записей содержащих данные в поле «ОбластьПолучателя», то есть, количество записей с непустым полем «ОбластьПолучателя». Для этого выполним следующий запрос:
SELECT count(*),count(ОбластьПолучателя) FROM Заказы;
В запросе используется агрегатная функция COUNT(). Используя агрегатные функции MAX(), MIN() и AVG(), составьте запрос для подсчёта максимальной минимальной и средней цены товара в таблице «Товары».
Используя агрегатную функцию SUM(), составьте запрос для подсчёта общей стоимости доставки всех заказанных товаров в таблице «Заказы».
Сохраните все созданные Вами запросы и покажите их преподавателю.
по значению столбца Дисциплина . Мы получим 4 группы, для которых можем вычислить некоторые групповые значения, например количество кортежей в группе, максимальное или минимальное значение столбца Оценка .| Функция | Результат |
|---|---|
| COUNT | Количество строк или непустых значений полей, которые выбрал запрос |
| SUM | Сумма всех выбранных значений данного поля |
| AVG | Среднеарифметическое значение всех выбранных значений данного поля |
| MIN | Наименьшее из всех выбранных значений данного поля |
| MAX | Наибольшее из всех выбранных значений данного поля |
| R1 | |||
|---|---|---|---|
| ФИО | Дисциплина | Оценка | |
| Группа 1 | Петров Ф. И. | Базы данных | 5 |
| Сидоров К. А. | Базы данных | 4 | |
| Миронов А. В. | Базы данных | 2 | |
| Степанова К. Е. | Базы данных | 2 | |
| Крылова Т. С. | Базы данных | 5 | |
| Владимиров В. А. | Базы данных | 5 | |
| Группа 2 | Сидоров К. А. | Теория информации | 4 |
| Степанова К. Е. | Теория информации | 2 | |
| Крылова Т. С. | Теория информации | 5 | |
| Миронов А. В. | Теория информации | Null | |
| Группа 3 | Трофимов П. А. | Сети и телекоммуникации | 4 |
| Иванова Е. А. | Сети и телекоммуникации | 5 | |
| Уткина Н. В. | Сети и телекоммуникации | 5 | |
| Группа 4 | Владимиров В. А. | Английский язык | 4 |
| Трофимов П. А. | Английский язык | 5 | |
| Иванова Е. А. | Английский язык | 3 | |
| Петров Ф. И. | Английский язык | 5 | |
Агрегатные функции используются подобно именам полей в операторе SELECT , но с одним исключением: они берут имя поля как аргумент . С функциями SUM и AVG могут использоваться только числовые поля. С функциями COUNT , MAX и MIN могут использоваться как числовые, так и символьные поля. При использовании с символьными полями MAX и MIN будут транслировать их в эквивалент ASCII кода и обрабатывать в алфавитном порядке. Некоторые СУБД позволяют использовать вложенные агрегаты, но это является отклонением от стандарта ANSI со всеми вытекающими отсюда последствиями.
Например, можно вычислить количество студентов, сдававших экзамены по каждой дисциплине. Для этого надо выполнить запрос с группировкой по полю "Дисциплина" и вывести в качестве результата название дисциплины и количество строк в группе по данной дисциплине. Применение символа * в качестве аргумента функции COUNT означает подсчет всех строк в группе.
SELECT R1.Дисциплина, COUNT(*) FROM R1 GROUP BY R1.Дисциплина
Результат:
Если же мы хотим сосчитать количество сдавших экзамен по какой-либо дисциплине, то нам необходимо исключить неопределенные значения из исходного отношения перед группировкой. В этом случае запрос будет выглядеть следующим образом:
Получим результат:
В этом случае строка со студентом
| Миронов А. В. | Теория информации | Null |
|---|
не попадет в набор кортежей перед группировкой, поэтому количество кортежей в группе для дисциплины " Теория информации " будет на 1 меньше.
Можно применять агрегатные функции также и без операции предварительной группировки, в этом случае все отношение рассматривается как одна группа и для этой группы можно вычислить одно значение на группу.
Обратившись снова к базе данных "Сессия" (таблицы R1, R2, R3 ), найдем количество успешно сданных экзаменов:
Это, конечно, отличается от выбора поля, поскольку всегда возвращается одиночное значение , независимо от того, сколько строк находится в таблице. Аргументом агрегатных функций могут быть отдельные столбцы таблиц. Но для того, чтобы вычислить, например, количество различных значений некоторого столбца в группе, необходимо применить ключевое слово DISTINCT совместно с именем столбца. Вычислим количество различных оценок, полученных по каждой дисциплине:
Результат:
В результат можно включить значение поля группировки и несколько агрегатных функций , а в условиях группировки можно использовать несколько полей. При этом группы образуются по набору заданных полей группировки. Операции с агрегатными функциями могут быть применены к объединению множества исходных таблиц. Например, поставим вопрос: определить для каждой группы и каждой дисциплины количество успешно сдавших экзамен и средний балл по дисциплине.
Результат:
Мы не можем использовать агрегатные функции в предложении WHERE , потому что предикаты оцениваются в терминах одиночной строки, а агрегатные функции - в терминах групп строк.
Предложение GROUP BY позволяет определять подмножество значений в особом поле в терминах другого поля и применять функцию агрегата к подмножеству. Это дает возможность объединять поля и агрегатные функции в едином предложении SELECT . Агрегатные функции могут применяться как в выражении вывода результатов строки SELECT , так и в выражении условия обработки сформированных групп HAVING . В этом случае каждая агрегатная функция вычисляется для каждой выделенной группы. Значения, полученные при вычислении агрегатных функций , могут быть использованы для вывода соответствующих результатов или для условия отбора групп.
Построим запрос , который выводит группы, в которых по одной дисциплине на экзаменах получено больше одной двойки:
В дальнейшем в качестве примера будем работать не с БД "Сессия", а с БД "Банк", состоящей из одной таблицы F , в которой хранится отношение F, содержащее информацию о счетах в филиалах некоторого банка:
F = (N, ФИО, Филиал, ДатаОткрытия, ДатаЗакрытия, Остаток); Q = (Филиал, Город);
поскольку на этой базе можно ярче проиллюстрировать работу с агрегатными функциями и группировкой.
Например, предположим, что мы хотим найти суммарный остаток на счетах в филиалах. Можно сделать раздельный запрос для каждого из них, выбрав SUM(Остаток) из таблицы для каждого филиала. GROUP BY , однако, позволит поместить их все в одну команду:
SELECT Филиал, SUM(Остаток) FROM F GROUP BY Филиал;
GROUP BY применяет агрегатные функции независимо для каждой группы, определяемой с помощью значения поля Филиал. Группа состоит из строк с одинаковым значением поля Филиал, и
