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 drove 1 000 miles in 100% electric car in 2 days (ish) Watch Here

30 days stats
Thank you to your donation!

21st Aug - John Chamblee ($50)
20th Aug - Eamon Heenan (£10)
20th Aug - Pawel Olejniczak ($20)
19th Aug - Robert French ($10)
19th Aug - Robert Kelly ($10)
16th Aug - Lee Mezzulo ($5)
8th Aug - Betrehail Keteam ($2)
7th Aug - Pawel Bednarski (£10)
27th Jul - Bernhard Lauber (€20)
----
15th Jul - Pawel Olejniczak (£20)
13th July - Thomas Tabert ($10)
30 days stats
#1: Server $82/month
#2: Video hosting $25/month
#3: Hotjar (Usability) $33/month
#4: TestPad (Testing) $9/month
#5: Katie & Emil $0.00/h
Want to help?

Caring is sharing :)