SQL GROUP BY

Want full access?

100% FREE, 4 508 fellow members
Video Description:

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:

 

SQL Group By Syntax

Below is group by syntax in the simpliest form:

SELECT FieldName

FROM TableName

GROUP BY FieldName

 

SQL Group By Example

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)


Group by example with Aggregate Function

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

 

SQL Group by with aggregate function

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

Did you find this page useful? +2  |  -0
(2 Votes)

Want to add a comment


no comments

We had our legal consultation, and today we'll send our request to create a 'not for profit' company, to companies house.

£5,000 is our first target to cover basic operational costs, investments and salary for next 2-3 months.

just 7 days left
£867 ($1,140)
raised of £5,000 target
17%
raised


Sharing is caring :)
Pawel Olejniczak donated £20
Thomas Tabert donated $10
 Katrina Kilcoyne donated £20
 Jason Batters donated £20

Official Sponsors