The
ProgrammingMSAccess.COM Site
A collection of Transact-sql scripts
for highlighting the use of the group by clause.
--Count number of customers grouped
--by country for all countries
SELECT Country, Count(CustomerID)
FROM Northwind..Customers
GROUP BY COUNTRY
--Count number of customers grouped
--by country for filtered list of customers
--beginning with @letter; use HAVING
DECLARE @letter char(1)
SET @letter = 'B'
SELECT Country, Count(CustomerID) 'No. of Customers'
FROM Northwind..Customers
GROUP BY COUNTRY
HAVING LEFT(Country,1) = @letter
--Count number of customers grouped
--by country for filtered list of customers
--beginning with @letter; use WHERE
DECLARE @letter char(1)
SET @letter = 'B'
SELECT Country, Count(CustomerID) 'No. of Customers'
FROM Northwind..Customers
WHERE LEFT(Country, 1) = @letter
GROUP BY COUNTRY
--Group extended price for all line items
--within each order
SELECT o.OrderID,
Sum(Cast(UnitPrice*Quantity*(1-Discount)AS dec(9,2)))
FROM Northwind..[Order Details] od
JOIN Northwind..Orders o
ON o.OrderID = od.OrderID
GROUP BY o.OrderID
--Group revenue by CompanyName within Country;
--order by CompanyName within Country
SELECT c.Country, c.CompanyName,
Sum(Cast(UnitPrice*Quantity*(1-Discount)AS dec(9,2)))
FROM Northwind..[Order Details] od
JOIN Northwind..Orders o
ON o.OrderID = od.OrderID
JOIN Northwind..Customers c
ON c.CustomerID = o.CustomerID
GROUP BY c.Country, c.CompanyName
ORDER BY c.Country, c.CompanyName
Want to understand SQL Server so that you can program it to
do more tasks like this? Get Programming Microsoft SQL Server 2000
with Microsoft Visual Basic .NET by Rick Dobson from Microsoft Press. Learn more about the book by clicking
here.
Copyright 2001 CAB, Inc. All rights reserved. Republication or redistribution
of CAB, Inc. content, including by framing or similar means, is expressly
prohibited without the prior written consent of CAB, Inc. CAB, Inc. shall not be
liable for any errors in the content, or for any actions taken in reliance
thereon.