Часто требуется создавать запросы, подобные следующему: "Сколько заказов поступило вчера?" При этом вас не интересует, кто оформил заказ, вы только хотите знать количество вчерашних заказов. Это можно сделать, используя запросы, группирующие итоговые функции.
Запросы с группировкой подводят итоги по одному или нескольким полям. Например, если вам интересно увидеть число заказов для каждого клиента, то нужно создать приведенный ниже запрос для таблицы tblOrder с группировкой данных по полю CustomerID.
SELECT CustomerID, COUNT(CustomerID) AS TotalOrders
FROM tblOrder
GROUP BY CustomerID
Результат выполнения такого запроса приведен ниже.
CustomerID TotalOrders 1 2 2 1 4 3 5 1 7 1 8 2Обратите внимание на использование предложения AS в этом выражении SQL. Оно предназначено для присвоения имени столбцу, содержащему результат итоговой функции, поскольку этот результат вычисляется, а не хранится в базе данных.
Для отображения имен клиентов вместо их идентификаторов нужно просто объединить с результатами запроса данные из таблицы tblCustomer.
SELECT tblOrder.CustomerID, FirstName, LastName
COUNT(dbo.tblOrder.CustomerID) AS TotalOrders
FROM tblOrder INNER JOIN tblCustomer
ON tblOrder.CustomerID = tblCustomer.ID
GROUP BY FirstName, LastName, CustomerID
После выполнения такого запроса будет получен приведенный ниже результат.
CustomerID FirstName LastName TotalOrders 1 John Smith 2 2 Jill Azalia 1 4 Daisy Klein 3 5 Dave Martin 1 7 Thurston Ryan 1 8 Jane Winters 2В этом случае предложение GROUP BY содержит поле CustomerID вместе с объединенными полями FirstName и LastName из таблицы tblCustomer. При использовании предложения GROUP BY в него необходимо включить все поля, по которым группируются извлекаемые записи. В данном случае идентификатор клиента и его имя участвуют в группировании данных и потому присутствуют в предложении GROUP BY. (К счастью, если вы забудете выполнить эту операцию, среда Visual Studio .NET автоматически предложит вам сделать это.)
Применение предложения HAVING для группирования данных в запросах
Как уже отмечалось выше, критерий запроса служит для ограничения количества извлекаемых записей. В обычных запросах для включения критериев используется предложение WHERE, в запросах с группированием — предложение HAVING. Эти предложения применяются совершенно одинаково, за исключением того, что HAVING относится к агрегированным строкам (т.е. к результату группирования), a WHERE – к отдельным строкам. Это довольно незначительное отличие, потому что в 9 случаях из 10 они дают совершенно одинаковый результат. Например для создания отчета о продажах клиента Jane с группированием данных можно использовать приведенный ниже запрос.
SELECT tblOrder.CustomerID, FirstName, LastName,
COUNT(dbo.tblOrder.CustomerID) AS TotalOrders
FROM tblOrder INNER JOIN tblCustomer
ON tblOrder.CustomerID = tblCustomer.ID
GROUP BY FirstName, LastName, CustomerID
HAVING FirstName = 'Jane'
Этот запрос возвращает одну запись для клиента Jane Winters с указанием двух сделанных ею заказов. Допустим, теперь нужно получить список активных покупателей, т.е. клиентов, сделавших более одного заказа. Поскольку агрегированное количество заказов хранится в вычисленном поле TotalOrders, можно предположить, что для определения таких клиентов допустимо использовать выражение HAVING TotalOrders > 1. К сожалению, это выражение некорректно, так как TotalOrders – это не поле базы данных, а вычисленное поле. Вместо этого следует включить данное вычисление в предложение HAVING показанного ниже запроса.
SELECT tblOrder.CustomerID, FirstName, LastName,
COUNT(dbo.tblOrder.CustomerID) AS TotalOrders
FROM tblOrder INNER JOIN tblCustomer
ON tblOrder.CustomerID = tblCustomer.ID
GROUP BY FirstName, LastName, CustomerID
HAVING (COUNT(tblOrder.CustomerID) > 1)
После выполнения этого запроса будут получены три строки, каждая из которых содержит номер, имя, фамилию и количество заказов для каждого клиента, который сделал более одного заказа.
CustomerID FirstName LastName TotalOrders 1 John Smith 2 4 Daisy Klein 3 8 Jane Winters 2Функция SUM
Ваши возможности в подведении итогов не ограничены простым подсчетом записей. Используя функцию SUM, можно генерировать итоговые результаты для всех возвращаемых записей по любым числовым полям. Например, для создания запроса, который генерирует итоги по количеству заказанных товаров каждым клиентом, необходимо написать следующую команду SQL:
SELECT OrderID, SUM(Quantity) AS TotalItems
FROM tblOrderItem