Vvmebel.com

Новости с мира ПК
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция count в access

Особенности функции 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. Вычислить суммарную стоимость всех вызовов.

Пример 30. Вычислить средний возраст для контактов.

Пример 31. Вычислить минимальную и максимальную длительность исходящих вызовов.

SELECT MIN(DLIT), MAX(DLIT)

FROM VYZOVY V, TIPY_VYZ T

WHERE (V.TIP_ID=T.ID) AND (T.NAZV=’Исходящий’)

Пример 32. Вывести дату и время самого первого вызова.

Пример 33. Вычислить количество контактов старше 30 лет.

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

Итоговый запрос из базы данных Access

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

1. В окне базы данных щелкните на кнопке Запросы.

2. Дважды щелкните на значке Создание запроса в режиме конструктора.

3. В открывшемся окне диалога (рис. 17.6) выделите строку Контакты.

4. Щелчком на кнопке Добавить добавьте выбранную таблицу в верхнюю область конструктора запроса.

5. Выделите пункт Список и снова щелкните на кнопке Добавить.

6. Щелчком на кнопке Закрыть закройте окно диалога Добавление таблицы. Списки полей двух таблиц, соединенные линией связи, появятся в окне конструктора.

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

Рис. 17.6 . Добавление таблицы

8. Перетащите поле Фамилия в ячейку Поле первого столбца конструктора.

9. В ту же ячейку второго столбца перетащите поле Имя таблицы Контакты.

10. В третий, четвертый и пятый столбцы бланка запроса перетащите поле Дата таблицы Список (рис. 17.7).

Рис. 17.7. Запрос с групповыми операциями

11. В раскрывающемся списке ячейки Групповая операция третьего столб ца бланка запроса выберите пункт Min.

12. В той же ячейке четвертого столбца выберите пункт Мах.

13. В пятом столбце задайте групповую операцию Count. Групповые операции построенного запроса обработают все записи таблицы Список, соответствующие конкретному человеку из таблицы Контакты, и вместо самих данных таблицы Список выведут в соответствующее поле результата запроса только значение величины, вычисляемой по определенной формуле. Доступные групповые операции перечислены в табл. 17.1.

ТАБЛИЦА 17.1 . Групповые операции

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

14. Щелкните на кнопке Вид, чтобы выполнить запрос. Появится таблица с пятью столбцами. Два первых столбца содержат фамилии и имена людей. По ним выполняется группировка, то есть расчет значений остальных полей запроса выполняется для записей таблицы Список, сопоставляемых с одним человеком. Как уже говорилось выше, соответствие контакта таблицы Список и человека из таблицы Контакты определяется полями Код_Контакты, с помощью которых осуществляется связь этих двух таблиц. Третий и четвертый столбцы запроса выводят соответственно дату первого (функция Min) и последнего (функция Мах) контакта с данным человеком. Пятый столбец содержит количество записей в таблице Список (функция Count), соответствующих. данному человеку, то есть число контактов с ним. Единственный недостаток построенного запроса — это непонятные имена столбцов. Давайте скорректируем их.

Читать еще:  Как запустить слайды в powerpoint автоматически

15. Щелчком на кнопке Вид вернитесь в конструктор запроса.

16. В ячейке Поле третьего столбца замените имя Дата на текст Дата первого контакта: Дата. Правая часть этого выражения, расположенная правее двоеточия, по-прежнему задает имя поля, а левая определяет название столбца результата запроса. Таким образом, любому столбцу запроса можно назначить произвольное имя.

17. В ячейке Поле четвертого столбца введите Дата последнего контакта: Дата.

18. В первой строке пятого столбца бланка запроса введите Число контактов: Дата.

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

19. Снова щелкните на кнопке Вид.

20. Закройте запрос.

21. Для сохранения изменений структуры щелкните на кнопке Да.

22. В окне диалога Сохранение введите имя Итоговый запрос и щелкните на кнопке ОК.

Функция count в access

Упражнение 4. Итоговый запрос

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

    В окне базы данных щелкните на кнопке Запросы.

    Дважды щелкните на значке Создание запроса в режиме конструктора (Create Query In Design View).

    В открывшемся окне диалога (рис. 17.6) выделите строку Контакты.

    4. Щелчком на кнопке Добавить добавьте выбранную таблицу в верхнюю область конструктора запроса.

    5. Выделите пункт Список и снова щелкните на кнопке Добавить.

    6. Щелчком на кнопке Закрыть закройте окно диалога Добавление таблицы (Show Table). Списки полей двух таблиц, соединенные линией связи, появятся в окне конструктора.

    7. Щелкните на кнопке Групповые операции (Totals) панели инструментов. В бланке запроса появится дополнительная строка Групповая операция: (Total), позволяющая выполнять статистические операции со значениями конкретных полей.

    Перетащите поле Фамилия в ячейку Поле (Field) первого столбца конструктора.

    В ту же ячейку второго столбца перетащите поле Имя таблицы Контакты.

    В третий, четвертый и пятый столбцы бланка запроса перетащите поле Дата таблицы Список (рис. 17.7).

    Рис. 17.7. Запрос с групповыми операциями

      В раскрывающемся списке ячейки Групповая операция (Total) третьего столб ца бланка запроса выберите пункт Min.

      В той же ячейке четвертого столбца выберите пункт Мах.

      В пятом столбце задайте групповую операцию Count. Групповые операции построенного запроса обработают все записи таблицы Список, соответствующие конкретному человеку из таблицы Контакты, и вместо самих данных таблицы Список выведут в соответствующее поле результата запроса только значение величины, вычисляемой по определенной формуле. Доступные групповые операции перечислены в табл. 17.1.

      ТАБЛИЦА 17.1 . Групповые операции

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

      Щелкните на кнопке Вид, чтобы выполнить запрос. Появится таблица с пятью столбцами. Два первых столбца содержат фамилии и имена людей. По ним выполняется группировка, то есть расчет значений остальных полей запроса выполняется для записей таблицы Список, сопоставляемых с одним человеком. Как уже говорилось выше, соответствие контакта таблицы Список и человека из таблицы Контакты определяется полями Код_Контакты, с помощью которых осуществляется связь этих двух таблиц. Третий и четвертый столбцы запроса выводят соответственно дату первого (функция Min) и последнего (функция Мах) контакта с данным человеком. Пятый столбец содержит количество записей в таблице Список (функция Count), соответствующих. данному человеку, то есть число контактов с ним. Единственный недостаток построенного запроса — это непонятные имена столбцов. Давайте скорректируем их.

      Щелчком на кнопке Вид вернитесь в конструктор запроса.

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

      В ячейке Поле четвертого столбца введите Дата последнего контакта: Дата.

      В первой строке пятого столбца бланка’запроса введите Число контактов: Дата.

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

      Снова щелкните на кнопке Вид.

      Для сохранения изменений структуры щелкните на кнопке Да.

      В окне диалога Сохранение (Save As) введите имя Итоговый запрос и щелкните на кнопке ОК.

      Лекция 6 Тема: Запросы. Вычисления и групповые операциию

      6.1. Вычисляемые поля.

      6.2. Создание выражений с помощью Построителя выражений.

      6.3. Обзор встроенных функций СУБД MS Access.

      6.4. Итоговые запросы.

      6.5. Перекрестные запросы.

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

      Читать еще:  Объект recordset не является обновляемым access

      В QBE СУБД MS Access такие возможности предоставляются через вычисляемые поля и групповые операции.

      6.1. Вычисляемые поля

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

      Стоимость: Товары! Цена * Количество * (1-Скидка)

      КоличествоМужчин: Sum(IIf(Пол = «м»; 1; 0))

      Стоимость, КоличествоМужчин и ФИО являются именами вычисляемых полей и отображаются в режиме таблицы в заголовке столбца, символ двоеточие исполняет роль разделителя между именем вычисляемого поля и выражением.

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

      Стоимость: Цена*[Количество товара]

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

      Обновить вычисленные результаты вручную невозможно.

      Для построения сложных выражений в СУБД MS Access входит утилита, называемая Построитель выражений.

      6.2. Создание выражений с помощью Построителя выражений

      Построитель выражений можно запустить щелкнув по клавише Построить на панели инструментов Конструктор запросов или выбрав в контекстном меню поля бланка запроса QBE команду меню Построить….

      Рис.6.1. Диалоговое окно Построитель выражений

      Рис.6.2. Диалоговое окно Построителя выражений
      со сформированным выражением

      6.3. Обзор встроенных функций СУБД MS Access

      СУБД MS Access содержит более 100 встроенных функций (рис.6.3), которые можно использовать при формировании вычисляемого поля или при задании условия отбора.

      Рис.6.3. Диалоговое окно Построителя выражений
      со списком встроенных функций

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

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

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

      Таблица 6.1

      Функции категории Дата/время

      Возвращает значение дня месяца от 1 до 31

      Возвращает значение месяца от 1 до 12

      Возвращает название месяца соответствующего номеру месяца: 1 – январь, 2 – февраль, и т. д. Если значение аргумента флаг Истина, то функция возвращает аббревиатуру месяца: 1 – янв, 2 – фев и т. д.

      Возвращает значение года от 100 до 9999

      Если аргумент число не указан, возвращает значение дня недели от 1 (воскресенье) до 7 (суббота). Если аргумент число имеет значение 0, то возвращает значение дня недели от 1 (понедельник) до 7 (воскресенье)

      Возвращает целое число от 0 од 23, представляющее значение часа

      Возвращает числовое значение в зависимости от значения аргумента интервал:

      «q» – квартал (от 1 до 4);

      «m» – месяц (от 1 до 12);

      «yyyy» – год (от 100 до 9999);

      «ww» – неделя (от 1 до 53);

      и т. п. (см. справку по функции).

      Возвращает текущую системную дату

      Функции категории Проверка

      Возвращает значение Истина если значение переменной (значение поля в источнике данных) равно Null

      Возвращает значение Истина если значение переменной (значение поля в источнике данных) имеет один из числовых типов данных

      Функции категории Управление

      IIf(условие; выр1; выр2)

      Возвращает значение выражения выр1 если значение аргумента условие Истина и возвращает значение выражения выр2 если значение аргумента условие Ложь. Данная функция аналогична функции ЕСЛИ в MS Excel.

      Продолжение таблицы 6.1

      Функции категории Текстовые

      Возвращает n левых символов аргумента текст

      Возвращает n правых символов аргумента текст

      Mid(текст; нач_поз[; n])

      Возвращает n символов начиная с позиции нач_поз аргумента текст. Если аргумент n не указан, то возвращает все символы до конца строки начиная с позиции нач_поз аргумента текст.

      Возвращает количество символов (длину строки) в аргументе текст

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

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

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

      Возвращает строковое значение аргумента число

      Возвращает значение аргумента переменная в формате заданным аргументом формат

      6.3. Итоговые запросы

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

      — количество сделок с Партнерами за определенный промежуток времени;

      — средний объем продаж по каждому месяцу за предыдущий год.

      Ответы на такие вопросы дает итоговый запрос.

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

      По умолчанию для каждого поля, занесенного в бланк запроса, устанавливается значение Группировка (итоги не подводятся).

      Для подведения итогов необходимо заменить установку Группировка на конкретную итоговую функцию. В СУБД MS Access предусмотрено 9 функций (табл. 6.2), обеспечивающих выполнение групповых операций.

      Читать еще:  Return without gosub access

      Таблица 6.2

      Функции категории Статистические

      Возвращает сумму набора значений

      Возвращает среднее арифметическое набора значений

      Возвращает наименьшее значение из набора значений

      Возвращает наибольшее значение из набора значений

      Возвращает количество записей в наборе значений отличных от Null

      Возвращает первое значение поля в группе

      Возвращает последнее значение поля в группе

      Возвращает среднеквадратичное отклонение набора значений

      Возвращает дисперсию набора значений

      В раскрывающемся списке строки Групповая операция имеется установка Выражение. Данная установка применяется, когда в выражении (строка Поле) используется несколько итоговых функций.

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

      Для решения более сложных статистических задач в СУБД MS Access предусмотрен специальный тип запроса – перекрестный запросов.

      6.4. Перекрестные запросы

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

      Для построения перекрестного запроса достаточно трех полей базового источника. По повторяющимся значениям одного поля формируются названия заголовков строк итоговой (сводной) таблицы (рис.6.4). По повторяющимся значениям другого поля формируются названия заголовков столбцов итоговой (сводной) таблицы. Результаты статистической обработки по третьему полю отображаются в ячейках сводной таблицы (область значений). Пример перекрестного запроса в режиме конструктора представлен на рис.6.5, а результаты выполнения запроса на рис. 6.6.

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

      В перекрестном запросе допускается указание условий отбора. Сортировка может проводиться только по полям, размещенным в области заголовков строк.

      Добавление сумм, счетчиков и итогов в отчет

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

      Использование функции «Sum»

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

      Функция Sum вычисляет итог по набору значений источника записей — в данном случае, столбца «Объем заказов». Sum — статистическая функция Access, которая позволяет вычислять итоговые значения.

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

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

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

      =[Цена за единицу]*0,085,

      чтобы рассчитать итог по значениям элемента управления «Налог», нужно записать

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

      Использование функции «Count»

      Пусть в отчет «Выручка по продуктам» нужно добавить счетчик количества заказов. Чтобы подсчитать число заказов, создается текстовое поле, а свойству ControlSource задается выражение, которое выглядит следующим образом:

      Звездочка означает, что Access будет проводить подсчет п о всем строкам области сведений, но результат будет зависеть от того, где будет размещено текстовое поле. Как и при подсчете суммы, способ работы счетчика в Access зависит от того, в каком разделе отчета будет находиться текстовое поле. Если разместить текстовое поле в заголовке или примечании группы, счетчик обработает только строки этой группы. Если текстовое поле будет находиться в заголовке или примечании отчета, счетчик обработает строки по всему отчету. На следующей диаграмме выражение «Count(*)» отображается в поле свойства ControlSource текстового поля, а итог, полученный по результатам работы функции Count, — в примечании отчета.

      Ссылка на основную публикацию
      Adblock
      detector