In this SQL tutorial I will cover SQL GROUP BY clause and give examples of how to use it. I will use SQL Server 2008 R2, but the examples in this tutorial should work with all SQL Server versions and some other databases. I will start with short and easy examples and explanations and move on to common group by questions that are slightly more involved.
Content:
Below is group by syntax in the simpliest form:
SELECT FieldName
FROM TableName
GROUP BY FieldName
Group By allows us to group rows together and additionally perform aggregate calculation which works on a group of rows. Let me first give you a simple example of what group by does using one field. I will use Customer table and group all customers by Country in other words I want to show all unique values that exist in Country Field, that is how group by works. See below example code with results (before and after)
SELECT *
You can group by fields in tables but this is rarely used on its own and very often that is combined with aggregate function. Aggregate function gets access to grouped rows. So if you group 3 rows into 1 then you can perform aggregate calculation like count which in this case would give result 3. You can also use other aggregate functions like SUM, MAX, MIN, AVG and several other ones. See below example that is the same as previous ones but now with Count aggregate function.
SELECT c.Country
,COUNT(*) as CustomerInEachCountry
,MAX(CustomerID) as TheHighestID
FROM dbo.Customer as c
GROUP BY c.Country
Compare this example to previous one and you should understand how group by and aggregate function work using SQL. The count is performed on grouped rows so UK appear in results only once as it was grouped but there are 3 rows that contain country UK so when aggregate function is used it is applied to all rows "inside" the group therefore the result is 3. I also used MAX function with CustomerID field to get the highest customerID in each group.
Take care
Emil
Consulting & Training Company
