Как найти сумму данных в базе данных - Сайт, где вы сможете решить свои вопросы

В данной статье описано использование агрегатной функции для суммирования данных в результатах запроса. В ней также кратко описывается использование других агрегатных функций, например COUNT и AVG, для подсчета или вычисления средних значений в результирующем наборе записей. Кроме того, в этой статье рассматривается использование строки итогов — возможности Access, позволяющий суммировать данные без изменения структуры запросов.

Выберите нужное действие

Общее представление о способах суммирования данных
Подготовка примера данных
Суммирования данных с помощью строки итогов
Вычисление общих итогов с помощью запроса
Вычисление итогов по группе с помощью итогового запроса
Суммирование данных из нескольких групп с помощью перекрестного запроса
Справочные сведения об агрегатных функциях

Общее представление о способах суммирования данных

Просуммировать числовой столбец в запросе можно с помощью агрегатной функции. Агрегатные функции выполняют вычисления со столбцами данных и возвращают единственное значение. В Access существует множество агрегатных функций, включая Sum, Count, Avg (для вычисления среднего значения), Min и Max. Суммирование данных производится путем добавления в запрос функции Sum, подсчет данных — путем использования функции Count и т. д.

Кроме того, в Access предусмотрено несколько способов добавления функции Sum и других агрегатных функций в запрос. Вы можете:

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

Город	2003	2004	2005
Краснодар	254 556	372 455	467 892
Санкт-Петербург	478 021	372 987	276 399
Москва	572 997	684 374	792 571
…	…	…	…

Примечание: Ниже в разделах этой статьи подробно описано применение функции Sum, однако следует помнить, что вы можете использовать другие агрегатные функции в строках итогов и запросах. Дополнительные сведения об использовании других агрегатных функций см. ниже в разделе Справочные сведения об агрегатных функциях.

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

В следующих разделах описано, как добавить строку итогов, использовать итоговый запрос для суммирования данных и перекрестный запрос, вычисляющий промежуточные итоги по группам и интервалам времени. Помните, что многие агрегатные функции работают только с данными в полях, имеющих определенный тип данных. Например, функция SUM работает только с типами данных “Число”, “Действительное” и “Денежный”. Дополнительные сведения о типах данных, требуемых для каждой функции, см. ниже в разделе Справочные сведения об агрегатных функциях.

Общие сведения о типах данных см. в статье Изменение типа данных для поля.

К началу страницы

Подготовка примера данных

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

Сделать это в Access можно несколькими способами. Вы можете ввести данные вручную, скопировать каждую таблицу в редактор электронных таблиц (такой как Excel) и импортировать листы в Access или же вставить данные в текстовый редактор, например Блокнот, и импортировать их из созданных текстовых файлов.

В пошаговых инструкциях этого раздела объясняется, как вводить данные вручную на пустой лист, а также как копировать примеры таблиц в редактор электронных таблиц и затем импортировать их в Access. Дополнительные сведения о создании и импорте текстовых данных см. в статье Импорт данных или связывание с данными текстового файла.

В пошаговых инструкциях в этой статье используются приведенные ниже таблицы. Создайте пример данных на их основе:

Таблица “Категории”

Категория
Куклы
Игры и головоломки
Картины и рамы
Видеоигры
DVD-диски и фильмы
Модели для сборки, хобби
Спортивное снаряжение

Таблицы “Товары”

Наименование товара	Цена	Категория
Фигурка программиста	12,95 ₽	Куклы
Эксперименты с C# (игра для всей семьи)	15,85 ₽	Игры и головоломки
Схема реляционной базы данных	22,50 ₽	Картины и рамы
Волшебная микросхема (500 деталей)	32,65 ₽	Игры и головоломки
Access! Игра!	22,95 ₽	Игры и головоломки
Компьютерные маньяки и мифические животные	78,50 ₽	Видеоигры
Упражнение для компьютерных мониторов! DVD-диск!	14,88 ₽	DVD-диски и фильмы
Неуловимая летающая пицца	36,75 ₽	Спортивное снаряжение
Внешний дисковод гибких дисков 5.25” (масштаб 1:4)	65,00 ₽	Модели для сборки, хобби
Недвижущаяся фигурка бюрократа	78,88 ₽	Куклы
Тьма	53,33 ₽	Видеоигры
Собери клавиатуру	77,95 ₽	Модели для сборки, хобби

Таблица Заказы

Дата заказа	Дата отгрузки	Город назначения	Стоимость доставки
14.11.2005	15.11.2005	Москва	55,00 ₽
14.11.2005	15.11.2005	Санкт-Петербург	76,00 ₽
16.11.2005	17.11.2005	Санкт-Петербург	87,00 ₽
17.11.2005	18.11.2005	Москва	43,00 ₽
17.11.2005	18.11.2005	Краснодар	105,00 ₽
17.11.2005	18.11.2005	Новосибирск	112,00 ₽
18.11.2005	19.11.2005	Иркутск	215,00 ₽
19.11.2005	20.11.2005	Владивосток	525,00 ₽
20.11.2005	21.11.2005	Иркутск	198,00 ₽
20.11.2005	21.11.2005	Краснодар	187,00 ₽
21.11.2005	22.11.2005	Санкт-Петербург	81,00 ₽
23.11.2005	24.11.2005	Москва	92,00 ₽

Таблица Сведения о заказах

Идентификатор заказа	Наименование товара	Код товара	Цена за единицу	Количество	Скидка
1	Собери клавиатуру	12	77,95 ₽	9	5%
1	Недвижущаяся фигурка бюрократа	2	78,88 ₽	4	7,5%
2	Упражнение для компьютерных мониторов! DVD-диск!	7	14,88 ₽	6	4%
2	Волшебная микросхема	4	32,65 ₽	8	0
2	Компьютерные маньяки и мифические животные	6	78,50 ₽	4	0
3	Access! Игра!	5	22,95 ₽	5	15%
4	Фигурка программиста	1	12,95 ₽	2	6%
4	Неуловимая летающая пицца	8	36,75 ₽	8	4%
5	Внешний дисковод гибких дисков 5.25” (масштаб 1:4)	9	65,00 ₽	4	10%
6	Схема реляционной базы данных	3	22,50 ₽	12	6,5%
7	Тьма	11	53,33 ₽	6	8%
7	Схема реляционной базы данных	3	22,50 ₽	4	9%

Примечание: Помните, что в типичной базе данных таблица “Сведения о заказах” будет содержать только поле “Код товара” (без поля “Наименование товара”). В данном примере поле “Наименование товара” используется для упрощения восприятия данных.

Ввод примеров данных вручную

на вкладке Создание в группе Таблицы нажмите кнопку Таблица.

Access добавит в базу данных новую пустую таблицу.

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

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

По мере ввода данных Access определяет их тип для каждого поля. Если вы плохо знакомы с реляционными базами данных, для каждого поля таблицы следует задать конкретный тип данных, например “Число”, “Текст” или “Дата/время”. Это обеспечивает точный ввод данных и помогает предотвратить ошибки, например использование цифр номера телефона в вычислениях. Для этих примеров таблиц можно определить тип данных автоматически.
Завершив ввод данных, нажмите кнопку Сохранить.

(Сочетание клавиш: нажмите CTRL+S.)

Откроется диалоговое окно Сохранение документа.
В поле Имя таблицы введите имя примера таблицы и нажмите кнопку ОК.

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

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

Создание листов с примерами данных

Запустите редактор электронных таблиц и создайте пустой файл. Если вы используете Excel, по умолчанию создается пустая книга.
Скопируйте первый пример таблицы и вставьте его на первый лист, начиная с первой ячейки.
Присвойте листу имя с помощью функций редактора электронных таблиц. Оно должно совпадать с именем примера таблицы. Например, если пример называется Категории, присвойте листу это же имя.
Повторяйте шаги 2 и 3, чтобы скопировать каждый пример таблицы на пустой лист и переименовать этот лист.

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

Создание таблиц базы данных на основе листов

На вкладке Внешние данные в группе Импорт щелкните Excel.

-или-

Нажмите кнопку Дополнительно, а затем выберите редактор электронных таблиц из списка.

Откроется диалоговое окно Внешние данные — лист <имя программы>.
Нажмите кнопку Обзор, откройте файл электронной таблицы, созданный на предыдущих этапах, и нажмите кнопку ОК.

Откроется окно мастера импорта электронных таблиц.
По умолчанию мастер выбирает первый лист в книге (в этом примере — лист Клиенты), и данные из этого листа появляются в нижней части страницы мастера. Нажмите кнопку Далее.
На следующей странице мастера выберите Первая строка содержит названия столбцов, а затем нажмите кнопку Далее.
Если нужно, вы можете изменить имена полей и типы данных или пропустить некоторые поля, воспользовавшись текстовыми полями и списками в группе Параметры поля. В противном случае нажмите кнопку Далее.
Оставьте параметр автоматически создать ключ выбранным и нажмите кнопку Далее.
По умолчанию Access использует имя листа для новой таблицы. Оставьте это имя или введите другое, а затем нажмите кнопку Готово.
Повторите шаги с 1 по 7 для каждого листа книги Excel, чтобы создать для него таблицу.

Переименование полей первичного ключа

Примечание: При импорте листов Access автоматически добавляет в каждую таблицу столбец первичного ключа и по умолчанию присваивает ему имя “Код” и тип данных “Счетчик”. В этом разделе даны инструкции по переименованию полей первичного ключа. Это позволяет четко определить все поля в запросе.

В области навигации щелкните правой кнопкой мыши каждую таблицу, созданную на предыдущем шаге, и выберите команду Конструктор.
Для каждой таблицы найдите поле первичного ключа. По умолчанию Access присваивает каждому полю имя Код.
В столбце Имя поля каждого поля первичного ключа добавьте имя таблицы.

Например, можно переименовать поле “ИД” в таблице “Категории” в “ИД категории”, а поле таблицы “Заказы” — в “ИД заказа”. В таблице “Сведения о заказе” переименуем поле в “Подробный ИД”. В таблице “Товары” переименуем поле в “ИД товара”.
Сохраните изменения.

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

К началу страницы

Суммирования данных с помощью строки итогов

Чтобы добавить в запрос строку итогов, откройте его в режиме таблицы, добавьте строку, а затем выберите нужную агрегатную функцию, например Sum, Min, Max или Avg. В этом разделе объясняется, как создать простой запрос на выборку и добавить строку итогов. Не обязательно использовать примеры таблиц, представленные в предыдущем разделе.

Создание простого запроса на выборку

На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.
Дважды щелкните таблицу или таблицы, которые вы хотите использовать в запросе.

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

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

Каждое поле отображается в ячейке в бланке запроса.
Нажмите кнопку Выполнить для выполнения запроса.

Результаты запроса будут отображены в режиме таблицы.
При необходимости переключитесь в Конструктор и скорректируйте запрос. Для этого щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Конструктор. После этого можно изменить запрос, добавив или удалив поля таблицы. Чтобы удалить поле, выберите столбец в бланке запроса и нажмите клавишу DELETE.
Сохраните запрос.

Добавление строки итогов

Убедитесь в том, что запрос открыт в режиме таблицы. Для этого щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Режим таблицы.

-или-

Дважды щелкните запрос в области навигации. Запрос будет выполнен, а его результаты будут загружены в таблицу.
На вкладке Главная в группе Записи нажмите кнопку Итоги.

В таблице появится новая строка Итог.
В строке Итог щелкните ячейку в поле, по которому вы хотите вычислить сумму, и выберите в списке функцию Sum.

Скрытие строки итогов

На вкладке Главная в группе Записи нажмите кнопку Итоги.

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

К началу страницы

Вычисление общих итогов с помощью запроса

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

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

В следующих разделах описано, как вычислить каждый из типов общих итогов. В инструкциях используются таблицы “Заказы” и “Сведения о заказах”.

Таблица “Заказы”

Идентификатор заказа	Дата заказа	Дата отгрузки	Город назначения	Стоимость доставки
1	14.11.2005	15.11.2005	Москва	55,00 ₽
2	14.11.2005	15.11.2005	Санкт-Петербург	76,00 ₽
3	16.11.2005	17.11.2005	Санкт-Петербург	87,00 ₽
4	17.11.2005	18.11.2005	Москва	43,00 ₽
5	17.11.2005	18.11.2005	Краснодар	105,00 ₽
6	17.11.2005	18.11.2005	Новосибирск	112,00 ₽
7	18.11.2005	19.11.2005	Иркутск	215,00 ₽
8	19.11.2005	20.11.2005	Владивосток	525,00 ₽
9	20.11.2005	21.11.2005	Иркутск	198,00 ₽
10	20.11.2005	21.11.2005	Краснодар	187,00 ₽
11	21.11.2005	22.11.2005	Санкт-Петербург	81,00 ₽
12	23.11.2005	24.11.2005	Москва	92,00 ₽

Таблица “Сведения о заказах”

Код сведений	Идентификатор заказа	Наименование товара	Код товара	Цена за единицу	Количество	Скидка
1	1	Собери клавиатуру	12	77,95 ₽	9	0,05
2	1	Недвижущаяся фигурка бюрократа	2	78,88 ₽	4	0,075
3	2	Упражнение для компьютерных мониторов! DVD-диск!	7	14,88 ₽	6	0,04
4	2	Волшебная микросхема	4	32,65 ₽	8	0,00
5	2	Компьютерные маньяки и мифические животные	6	78,50 ₽	4	0,00
6	3	Access! Игра!	5	22,95 ₽	5	0,15
7	4	Фигурка программиста	1	12,95 ₽	2	0,06
8	4	Неуловимая летающая пицца	8	36,75 ₽	8	0,04
9	5	Внешний дисковод гибких дисков 5.25” (масштаб 1:4)	9	65,00 ₽	4	0,10
10	6	Схема реляционной базы данных	3	22,50 ₽	12	0,065
11	7	Тьма	11	53,33 ₽	6	0,08
12	7	Схема реляционной базы данных	3	22,50 ₽	4	0,09

Вычисление простого общего итога

На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.
Дважды щелкните таблицу, которую вы хотите использовать в запросе.

При использовании примера данных нужно дважды щелкнуть таблицу “Заказы”.

Таблица появится в окне в верхней части конструктора запросов.
Дважды щелкните поле, для которого вы хотите найти сумму. Убедитесь, что поле имеет тип данных “Число” или “Денежный”. При попытке суммировать значения в нечисловых полях, например в текстовом поле, Access выводит сообщение об ошибке Несоответствие типов данных в выражении условия отбора.

Если вы используете пример данных, дважды щелкните столбец “Стоимость доставки”.

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

В бланке появится строка Итог, а в ячейке столбца “Стоимость доставки” будет указано Группировка.
Измените значение в ячейке строки Итог строки на Sum.
Чтобы выполнить запрос и отобразить результаты в режиме таблицы, нажмите кнопку Выполнить .

Совет: Обратите внимание, что Access добавит “СуммаOf” в начало имени поля, которое вы суммируете. Чтобы изменить заголовок столбца на более осмысленный, например “Всего доставки”, переключиться обратно в конструктор и щелкните в строке “Поле” столбца “Стоимость доставки” в сетке конструктора. Поместите курсор рядом с искомой стоимостью доставки и введите слова Total Shippingи двоеточие, например: Total Shipping: Shipping Fee.
При необходимости вы можете сохранить запрос и закрыть его.

Вычисление общего итога за исключением нескольких записей

На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.
Дважды щелкните таблицы “Заказы” и “Сведения о заказах”.
Добавьте поле “Дата заказа” из таблицы “Заказы” в первый столбец на бланке запроса.
В строке Условие отбора первого столбца введите Date() -1. Это выражение исключает записи с текущей датой из вычисляемого итогового значения.
Затем создайте столбец, который вычисляет объем продаж по каждой транзакции. Введите в строку Поле второго столбца на бланке следующее выражение:

Общее значение продаж: (1-[Сведения о заказах].[Скидка]/100)*([Сведения о заказах].[Цена за единицу]*[Сведения о заказах].[Количество])

Убедитесь, что выражение ссылается на поля с типами данных “Число” или “Денежный”. Если оно ссылается на поля с другими типами данных, то при попытке выполнения запроса появится сообщение Несоответствие типов данных в выражении условия отбора.
На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Итоги.

В бланке появится строка Итог, а в первом и втором столбцах будет указано Группировка.
Во втором столбце измените значение в ячейке строки Итог строки на Sum. Функция Sum суммирует отдельные показатели продаж.
Чтобы выполнить запрос и отобразить результаты в режиме таблицы, нажмите кнопку Выполнить .
Сохраните запрос с именем Продажи за день.

Примечание: При следующем открытии запроса в Конструкторе можно заметить небольшие изменения в значениях, указанных для строк Поле и Итог в столбце “Общее значение продаж”. Выражение заключено в функцию Sum, а в строке Итог выводится Выражение вместо функции Sum.

Например, если вы использовали пример данных и создали запрос, как показано выше, будет отображено:

Общее значение продаж: Sum((1-[Сведения о заказах].Скидка/100)*([Сведения о заказах].Цена*[Сведения о заказах].Количество))

К началу страницы

Вычисление итогов по группе с помощью итогового запроса

В этом разделе описано, как создать итоговый запрос для вычисления промежуточных итогов по группам данных. Помните, что по умолчанию итоговый запрос может включать только поле или поля, содержащие данные, по которым выполняется группировка, например поле “Категории”, а также поля со значениями, которые вы хотите просуммировать, например поле “Продажи”. Итоговые запросы не могут включать другие поля, описывающие значения внутри категории. Если вы хотите отобразить эти описательные данные, создайте второй запрос на выборку, объединяющий поля из итогового запроса с полями, содержащими дополнительные данные.

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

Таблицы “Товары”

Код товара	Наименование товара	Цена	Категория
1	Фигурка программиста	12,95 ₽	Куклы
2	Эксперименты с C# (игра для всей семьи)	15,85 ₽	Игры и головоломки
3	Схема реляционной базы данных	22,50 ₽	Картины и рамы
4	Волшебная микросхема (500 деталей)	32,65 ₽	Картины и рамы
5	Access! Игра!	22,95 ₽	Игры и головоломки
6	Компьютерные маньяки и мифические животные	78,50 ₽	Видеоигры
7	Упражнение для компьютерных мониторов! DVD-диск!	14,88 ₽	DVD-диски и фильмы
8	Неуловимая летающая пицца	36,75 ₽	Спортивное снаряжение
9	Внешний дисковод гибких дисков 5.25” (масштаб 1:4)	65,00 ₽	Модели для сборки, хобби
10	Недвижущаяся фигурка бюрократа	78,88 ₽	Куклы
11	Тьма	53,33 ₽	Видеоигры
12	Собери клавиатуру	77,95 ₽	Модели для сборки, хобби

Таблица “Сведения о заказах”

Код сведений	Идентификатор заказа	Наименование товара	Код товара	Цена за единицу	Количество	Скидка
1	1	Собери клавиатуру	12	77,95 ₽	9	5%
2	1	Недвижущаяся фигурка бюрократа	2	78,88 ₽	4	7,5%
3	2	Упражнение для компьютерных мониторов! DVD-диск!	7	14,88 ₽	6	4%
4	2	Волшебная микросхема	4	32,65 ₽	8	0
5	2	Компьютерные маньяки и мифические животные	6	78,50 ₽	4	0
6	3	Access! Игра!	5	22,95 ₽	5	15%
7	4	Фигурка программиста	1	12,95 ₽	2	6%
8	4	Неуловимая летающая пицца	8	36,75 ₽	8	4%
9	5	Внешний дисковод гибких дисков 5.25” (масштаб 1:4)	9	65,00 ₽	4	10%
10	6	Схема реляционной базы данных	3	22,50 ₽	12	6,5%
11	7	Тьма	11	53,33 ₽	6	8%
12	7	Схема реляционной базы данных	3	22,50 ₽	4	9%

Следующие действия также предполагают наличие отношения “один-ко-многим” между полями “Код товара” в таблицах “Заказы” и “Сведения о заказах” с таблицей “Заказы” на стороне “один” данного отношения.

Создание итогового запроса

На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.
Выберите нужные таблицы и нажмите кнопку “Добавить”.

Таблица появится в виде окна в верхней части конструктора запросов.

При использовании примеров таблиц, указанных выше, добавьте таблицы “Товары” и “Сведения о заказах”.
Дважды щелкните поля таблицы, которые вы хотите использовать в запросе.

Как правило, в запрос добавляются только поле группы и поле значений. Однако вместо поля значения можно использовать вычисление — в следующих действиях объясняется, как это сделать.
1. Добавьте поле “Категория” из таблицы “Товары” в бланк запроса.
2. Создайте столбец, вычисляющий объемы продаж для каждой транзакции, введя во второй столбец бланка запроса следующее выражение:
  
  Общее значение продаж: (1-[Сведения о заказах].[Скидка]/100)*([Сведения о заказах].[Цена за единицу]*[Сведения о заказах].[Количество])
  
  Убедитесь, что поля, на которые ссылается выражение, имеют типы данных “Число” или “Денежный”. Если оно ссылается на поля с другими типами данных, то при попытке переключения в режим таблицы появится сообщение об ошибке Несоответствие типов данных в выражении условия отбора.
3. На вкладке Конструктор в группе Показать или скрыть нажмите кнопку Итоги.
  
  В бланке появится строка Итог, в первом и втором столбцах которой будет указано Группировка.
4. Во втором столбце измените значение в строке Итог строки на Sum. Функция Sum суммирует отдельные показатели продаж.
5. Чтобы выполнить запрос и отобразить результаты в режиме таблицы, нажмите кнопку Выполнить .
6. Оставьте запрос открытым, чтобы использовать его в следующем разделе.
  
  Использование условий в итоговом запросе
  
  Запрос, созданный в предыдущем разделе, включает все записи в базовых таблицах. Он не исключает никакие заказы при вычислении итогов и отображает итоги для всех категорий.
  
  Если вам нужно исключить некоторые записи, можно добавить условия в запрос. Например, вы можете пропустить транзакции с суммой менее 100 ₽ или вычислить итоги только по некоторым категориям товаров. В этом разделе описано использование трех типов условий:
7. Условия, игнорирующие некоторые группы при вычислении итогов. Например, можно вычислить итоги только для категорий товаров “Видеоигры”, “Картины и рамы” и “Спортивное снаряжение”.
8. Условия, скрывающие некоторые итоговые значения после их вычисления. Например, можно отобразить только итоговые значения свыше 150 000 ₽.
9. Условия, исключающие некоторые записи при вычислении итогового значения. Например, можно исключить отдельные транзакции, в которых значение (Цена за единицу * Количество) меньше 100 ₽.
  
  На примере следующих действий объясняется, как добавить условия одно за другим и как это повлияет на результаты запроса.
  
  Добавление условий в запрос
10. Откройте запрос из предыдущего раздела в Конструкторе. Для этого щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Конструктор.
  
  -или-
  
  Щелкните правой кнопкой мыши запрос в области навигации и выберите Конструктор.
11. В строке Условия отбора столбца “Код категории” введите =Куклы Or Спортивное снаряжение or Картины и рамы.
12. Чтобы выполнить запрос и отобразить результаты в режиме таблицы, нажмите кнопку Выполнить .
13. Вернитесь в Конструктор и в строке Условия отбора столбца “Общее значение продаж” введите >100.
14. Выполните запрос, чтобы просмотреть результаты, а затем переключитесь в Конструктор.
15. Теперь добавьте условия для исключения отдельных транзакций, сумма которых меньше 100 ₽. Для этого необходимо добавить еще один столбец.
  
  Примечание: Невозможно указать третье условие для столбца “Общее значение продаж”. Любое условие, указанное для этого столбца, будет применено к итоговому значению, а не к отдельным значениям.
16. Скопируйте выражение из второго столбца в третий столбец.
17. В строке Итог нового столбца выберите Условие, а в строке Условия отбора введите >20.
18. Выполните запрос, чтобы просмотреть результаты, а затем сохраните его.
  
  Примечание: При следующем открытии запроса в Конструкторе можно заметить небольшие изменения в бланке. Во втором столбце выражение в строке Поле будет заключено в функцию Sum, а в строке Итоги будет указано Выражение вместо функции Sum.
  
  Общее значение продаж: Sum((1-[Сведения о заказах].Скидка/100)*([Сведения о заказах].Цена*[
  Сведения о заказах].Количество))
  
  Будет также отображен четвертый столбец. Этот столбец — копия второго столбца, но условия, заданные во втором столбце, на самом деле отображаются как часть нового столбца.

К началу страницы

Суммирование данных из нескольких групп с помощью перекрестного запроса

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

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

При создании перекрестного запроса обычно включаются данные из нескольких таблиц, и всегда включаются три типа данных: данные, используемые в качестве заголовков строк, данные, используемые в качестве заголовков столбцов и значения, которые вы хотите просуммировать или с которыми необходимо произвести другие вычисления.

Инструкции в данном разделе предполагают использование следующих примеров таблиц:

Таблица “Заказы”

Дата заказа	Дата отгрузки	Город назначения	Стоимость доставки
14.11.2005	15.11.2005	Москва	55,00 ₽
14.11.2005	15.11.2005	Санкт-Петербург	76,00 ₽
16.11.2005	17.11.2005	Санкт-Петербург	87,00 ₽
17.11.2005	18.11.2005	Москва	43,00 ₽
17.11.2005	18.11.2005	Краснодар	105,00 ₽
17.11.2005	18.11.2005	Новосибирск	112,00 ₽
18.11.2005	19.11.2005	Иркутск	215,00 ₽
19.11.2005	20.11.2005	Владивосток	525,00 ₽
20.11.2005	21.11.2005	Иркутск	198,00 ₽
20.11.2005	21.11.2005	Краснодар	187,00 ₽
21.11.2005	22.11.2005	Санкт-Петербург	81,00 ₽
23.11.2005	24.11.2005	Москва	92,00 ₽

Таблица “Сведения о заказах”

Идентификатор заказа	Наименование товара	Код товара	Цена за единицу	Количество	Скидка
1	Собери клавиатуру	12	77,95 ₽	9	5%
1	Недвижущаяся фигурка бюрократа	2	78,88 ₽	4	7,5%
2	Упражнение для компьютерных мониторов! DVD-диск!	7	14,88 ₽	6	4%
2	Волшебная микросхема	4	32,65 ₽	8	0
2	Компьютерные маньяки и мифические животные	6	78,50 ₽	4	0
3	Access! Игра!	5	22,95 ₽	5	15%
4	Фигурка программиста	1	12,95 ₽	2	6%
4	Неуловимая летающая пицца	8	36,75 ₽	8	4%
5	Внешний дисковод гибких дисков 5.25” (масштаб 1:4)	9	65,00 ₽	4	10%
6	Схема реляционной базы данных	3	22,50 ₽	12	6,5%
7	Тьма	11	53,33 ₽	6	8%
7	Схема реляционной базы данных	3	22,50 ₽	4	9%

Ниже объясняется, как создавать перекрестный запрос, группирующий итоги продаж по городам. Запрос использует два выражения для возврата форматированной даты и общего объема продаж.

Создание перекрестного запроса

На вкладке Создать в группе Другое нажмите кнопку Конструктор запросов.
Дважды щелкните таблицы, которые вы хотите использовать в запросе.

Таблица появится в виде окна в верхней части конструктора запросов.

При использовании примеров таблиц дважды щелкните таблицы “Заказы” и “Сведения о заказах”.
Дважды щелкните поля, которые вы хотите использовать в запросе.

Каждое имя поля появится в пустой ячейке в строке Поле на бланке.

При использовании примеров таблиц добавьте поля “Город назначения” и “Дата отгрузки” из таблицы “Заказы”.
В следующую пустую ячейку строки Поле скопируйте и вставьте или введите следующее выражение: Итоги продаж: Sum(CCur([Сведения о заказах].[Цена за едницу]*[Количество]*(1-[Скидка])/100)*100)
На вкладке Конструктор в группе Тип запроса щелкните элемент Перекрестная таблица.

В бланке запроса будут отображены строки Итог и Перекрестный.
Щелкните ячейку в строке Итог в поле “Город назначения” и выберите Группировка. Выполните те же действия для поля “Дата отгрузки”. Измените значение в ячейке Итоги поля “Итоги продаж” на Выражение.
В строке Перекрестный присвойте ячейке в поле “Город назначения” значение Заголовки строк, полю “Дата отгрузки” — значение Заголовки столбцов, а полю “Итоги продаж” — Значение.
На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.

Результаты запроса будут отображены в режиме таблицы.

К началу страницы

Справочные сведения об агрегатных функциях

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

Функция	Описание	Поддерживаемые типы данных
Average	Вычисляет среднее значение для столбца. Столбец должен содержать числовые или денежные величины или значения даты или времени. Функция игнорирует пустые значения.	“Число”, “Денежный”, “Дата/время”
Count	Подсчитывает число элементов в столбце.	Все типы данных, за исключением сложных повторяющихся скалярных данных, таких как столбец многозначных списков. Дополнительные сведения о списках, которые могут быть многоценными, см. в руководстве по полям, которые могут быть многоценными, а также к созданию или удалите многофаентное поле.
Максимум	Возвращает элемент, имеющий наибольшее значение. Для текстовых данных наибольшим будет последнее по алфавиту значение, причем Access не учитывает регистр. Функция игнорирует пустые значения.	“Число”, “Денежный”, “Дата/время”
Minimum	Возвращает элемент, имеющий наименьшее значение. Для текстовых данных наибольшим будет первое по алфавиту значение, причем Access не учитывает регистр. Функция игнорирует пустые значения.	“Число”, “Денежный”, “Дата/время”
Standard Deviation	Показывает, насколько значения отклоняются от среднего. Дополнительные сведения об этой функции см. в статье Отображение итогов по столбцу в таблице.	“Число”, “Денежный”
Sum	Суммирует элементы в столбце. Подходит только для числовых и денежных данных.	“Число”, “Денежный”
Variance	Вычисляет статистическую дисперсию для всех значений в столбце. Подходит только для числовых и денежных данных. Если таблица содержит менее двух строк, Access возвращает пустое значение. Дополнительные сведения о функциях для расчета дисперсии см. в разделе Отображение итогов по столбцу в таблице.	“Число”, “Денежный”

К началу страницы

Источник

In this article, we will look into the process of querying the sum of all values in a column of a database table. But before finding the sum of all values in a column, let us create a table with some columns and data. In this article, we will be using the Microsoft SQL Server as our database.

Creating a table :

Use the below syntax to create a table inside the database

Syntax :

create table table_name( column_name 1 data type ( size ) ,
        column_name 2 data type ( size) ,
          . . . . column_name n data type ( size ) )

For the sake of illustration, we will be creating a department table and operate on the same. The department table will have 3 fields namely deptid, deptname, totalemployees. To do so use the below statement:

CREATE TABLE department( deptid integer ,
                 deptname varchar(20) ,
                  totalemployees integer );

This will create the table. To insert values into the table we need to use the INSERT statement. So let us see add some data to the department table that we created:

Note: We have to insert values according to the table created. For example, we created a department table with deptid as integer, deptname as varchar, and totalemployees as an integer. So, we need to insert an integer, a character, and an integer respectively.

Now let us insert some rows into the department table using the below query:

INSERT INTO department values(1,'IT',32);
INSERT INTO department values(2,'CSE',56);
INSERT INTO department values(1,'ECE',28);

Output:

Following the same pattern we have inserted some rows into the table, now let us print the data available in the table using the SELECT statement as shown below:

SELECT * FROM department;

Note: Here * represents all. If we execute this query, the entire table will be displayed.

Output :

Sum of all values in a column:

For this, we need to use the sum() function. We have to pass the column name as a parameter.
This sum() function can be used with the SELECT query for retrieving data from the table.
The below example shows to find the sum of all values in a column.

Example :

SELECT SUM(totalemployees) FROM department;

Output :

Conclusion: Using the sum() function we can get the sum of values in a column using the column name.

Last Updated :
13 Apr, 2021

Like Article

Save Article

Источник

Агрегатные функции

Последнее обновление: 21.05.2018

Агрегатные функции вычисляют некоторые скалярные значения в наборе строк. В MySQL есть следующие агрегатные функции:

AVG: вычисляет среднее значение
SUM: вычисляет сумму значений
MIN: вычисляет наименьшее значение
MAX: вычисляет наибольшее значение
COUNT: вычисляет количество строк в запросе

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

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

Все агрегатные функции за исключением COUNT(*) игнорируют значения NULL.

Avg

Функция Avg возвращает среднее значение на диапазоне значений столбца таблицы.

Например, пусть есть следующая таблица товаров Products:

CREATE TABLE Products
(
    Id INT AUTO_INCREMENT PRIMARY KEY,
    ProductName VARCHAR(30) NOT NULL,
    Manufacturer VARCHAR(20) NOT NULL,
    ProductCount INT DEFAULT 0,
    Price DECIMAL NOT NULL
);
  
INSERT INTO Products(ProductName, Manufacturer, ProductCount, Price) 
VALUES
('iPhone X', 'Apple', 3, 76000),
('iPhone 8', 'Apple', 2, 51000),
('iPhone 7', 'Apple', 5, 32000),
('Galaxy S9', 'Samsung', 2, 56000),
('Galaxy S8', 'Samsung', 1, 46000),
('Honor 10', 'Huawei', 5, 28000),
('Nokia 8', 'HMD Global', 6, 38000)

Найдем среднюю цену товаров из базы данных:

SELECT AVG(Price) AS Average_Price FROM Products

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

На этапе выборки можно применять фильтрацию. Например, найдем среднюю цену для товаров определенного производителя:

SELECT AVG(Price) FROM Products
WHERE Manufacturer='Apple'

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

SELECT AVG(Price * ProductCount) FROM Products

Count

Функция Count вычисляет количество строк в выборке. Есть две формы этой функции. Первая форма COUNT(*) подсчитывает число строк в выборке:

SELECT COUNT(*) FROM Products

Вторая форма функции вычисляет количество строк по определенному столбцу, при этом строки со значениями NULL игнорируются:

SELECT COUNT(Manufacturer) FROM Products

Min и Max

Функции Min и Max вычисляют минимальное и максимальное значение по столбцу соответственно.
Например, найдем минимальную цену среди товаров:

SELECT MIN(Price), MAX(Price) FROM Products

Данные функции также игнорируют значения NULL и не учитывают их при подсчете.

Sum

Функция Sum вычисляет сумму значений столбца. Например, подсчитаем общее количество товаров:

SELECT SUM(ProductCount) FROM Products

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

SELECT SUM(ProductCount * Price) FROM Products

All и Distinct

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

SELECT COUNT(DISTINCT Manufacturer) FROM Products

По умолчанию вместо DISTINCT применяется оператор ALL, который выбирает все строки:

SELECT COUNT(ALL Manufacturer) FROM Products

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

Так как этот оператор неявно подразумевается при отсутствии DISTINCT, то его можно не указывать.

Комбинирование функций

Объединим применение нескольких функций:

SELECT COUNT(*) AS ProdCount,
	   SUM(ProductCount) AS TotalCount,
       MIN(Price) AS MinPrice,
       MAX(Price) AS MaxPrice,
       AVG(Price) AS AvgPrice
FROM Products

Источник

Агрегатные функции

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

SUM рассчитывает сумму

Вычислим сумму полей «Quantity» в таблице «OrderDetails».

Пишем слово SELECT, а затем название агрегатной функции SUM(). В скобках указываем, значения какого столбца мы хотим суммировать. Дальше пишем оператор FROM и имя таблицы.

SELECT SUM(Quantity) FROM OrderDetails;

Функция SUM() вернула общую сумму указанного столбца.

MAX рассчитывает максимальное значение

Найдем с помощью оператора MAX() самую большую стоимость продукта.

SELECT MAX(Price) FROM Products;

Функция MAX нашла цену самого дорогого продукта в столбце Price из таблицы Products.

MIN рассчитывает минимальное значение

Найдем с помощью оператора MIN() самую маленькую стоимость продукта.

SELECT MIN(Price) FROM Products;

Функция MIN нашла цену самого дешевого продукта в столбце Price из таблицы Products.

COUNT подсчитывает количество значений

Функция COUNT() возвращает количество строк, которое соответствует заданному критерию. Давайте выведем общее количество товаров, хранящихся в базе данных. Проще всего подсчитать количество продуктов по ID.

SELECT COUNT(ProductID) FROM Products;

AVG рассчитывает среднее значение

Функция AVG() возвращает среднее значение чисел в указанном столбце.

SELECT AVG(Price) FROM Products;

Группировка данных Group By

Ключевое слово Group By чаще всего используется вместе с агрегатными функциями, чтобы группировать столбцы по тому или иному признаку. Например, нам нужно подсчитать количество клиентов в базе из каждой страны по отдельности.

Пишем оператор SELECT и рассчитываем количество клиентов по столбцу CustomerID для каждой страны и группируем результат расчета по странам. Например из Аргентины у нас 3 клиента, а из Бельгии – 2 клиента.

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;

Упорядочим наш список и отсортируем клиентов в порядке убывания. Нам нужно знать, в каких странах живет всех больше клиентов.

SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country ORDER BY COUNT(CustomerID) DESC;

Всех больше клиентов проживает в USA.

Создано 30.04.2021 10:11:41
Михаил Русаков

Копирование материалов разрешается только с указанием автора (Михаил Русаков) и индексируемой прямой ссылкой на сайт (http://myrusakov.ru)!

Добавляйтесь ко мне в друзья ВКонтакте: http://vk.com/myrusakov.
Если Вы хотите дать оценку мне и моей работе, то напишите её в моей группе: http://vk.com/rusakovmy.

Если Вы не хотите пропустить новые материалы на сайте,
то Вы можете подписаться на обновления: Подписаться на обновления

Если у Вас остались какие-либо вопросы, либо у Вас есть желание высказаться по поводу этой статьи, то Вы можете оставить свой комментарий внизу страницы.

Если Вам понравился сайт, то разместите ссылку на него (у себя на сайте, на форуме, в контакте):

Кнопка:

Она выглядит вот так:
Текстовая ссылка:

Она выглядит вот так: Как создать свой сайт
BB-код ссылки для форумов (например, можете поставить её в подписи):

Источник

Функция SUM суммирует значения
указанного поля по всем выбранным строкам.

К примеру, если у нас есть поле ‘зарплата’, мы можем
найти суммарную зарплату всех работников.

См. также функцию AVG,
которая находит среднее арифметическое (сумма, деленная на количество).

См. также функцию MAX,
которая возвращает максимальное значение.

См. также функцию MIN,
которая возвращает минимальное значение.

Синтаксис

SELECT SUM(поле) FROM имя_таблицы WHERE условие

Примеры

Все примеры будут по этой таблице workers, если не сказано иное:

id айди	name имя	age возраст	salary зарплата
1	Дима	23	100
2	Петя	24	200
3	Вася	25	300

Пример

Давайте найдем суммарную зарплату всех работников:

SELECT SUM(salary) as sum FROM workers

Результат выполнения SQL запроса:

sum суммарная зарплата
600

Пример

Давайте найдем суммарную зарплату тех работников,
у которых id меньше 3-х:

SELECT SUM(salary) as sum FROM workers WHERE id<3

Результат выполнения SQL запроса:

sum суммарная зарплата
300

Источник

Выберите нужное действие

Общее представление о способах суммирования данных

Подготовка примера данных

Ввод примеров данных вручную

Создание листов с примерами данных

Создание таблиц базы данных на основе листов

Переименование полей первичного ключа

Суммирования данных с помощью строки итогов

Создание простого запроса на выборку

Добавление строки итогов

Скрытие строки итогов

Вычисление общих итогов с помощью запроса

Вычисление простого общего итога

Вычисление общего итога за исключением нескольких записей

Вычисление итогов по группе с помощью итогового запроса

Создание итогового запроса

Суммирование данных из нескольких групп с помощью перекрестного запроса

Создание перекрестного запроса

Справочные сведения об агрегатных функциях

Creating a table :

Sum of all values in a column:

Агрегатные функции

Avg

Count

Min и Max

Sum

All и Distinct

Комбинирование функций

Агрегатные функции

SUM рассчитывает сумму

MAX рассчитывает максимальное значение

MIN рассчитывает минимальное значение

COUNT подсчитывает количество значений

AVG рассчитывает среднее значение

Группировка данных Group By

Синтаксис

Примеры

Пример

Пример

Вам также может понравиться

Как найти образ отрезка

Как составить поэтический орфоэпический словарь

Как найти канал романтичное

Добавить комментарий Отменить ответ