write group by

Home » SQL Tutorial » SQL GROUP BY

SQL GROUP BY

Summary : in this tutorial, you will learn how to use the SQL GROUP BY clause to group rows based on one or more columns.

Introduction to SQL GROUP BY clause

The GROUP BY is an optional clause of the SELECT statement. The GROUP BY clause allows you to group rows based on values of one or more columns. It returns one row for each group.

The following shows the basic syntax of the GROUP BY clause:

The following picture illustrates shows how the GROUP BY clause works:

The table on the left side has two columns id and fruit . When you apply the GROUP BY clause to the fruit column, it returns the result set that includes unique values from the fruit column:

In practice, you often use the GROUP BY clause with an aggregate function such as MIN , MAX , AVG , SUM , or COUNT to calculate a measure that provides the information for each group.

For example, the following illustrates how the GROUP BY clause works with the COUNT aggregate function:

In this example, we group the rows by the values of the fruit column and apply the COUNT function to the id column. The result set includes the unique values of the fruit columns and the number of the corresponding rows.

The columns that appear in the GROUP BY clause are called grouping columns . If a grouping column contains NULL values, all NULL values are summarized into a single group because the GROUP BY clause considers all NULL values equal.

SQL GROUP BY examples

We will use the employees and departments tables in the sample database to demonstrate how the GROUP BY clause works.

The following example uses the GROUP BY clause to group the values in department_id column of the employees table:

In this example:

• First, the SELECT clause returns all values from the department_id column of employees table.
• Second, the GROUP BY clause groups all values into groups.

The department_id column of the employees table has 40 rows, including duplicate department_id values. However, the GROUP BY groups these values into groups.

Without an aggregate function, the GROUP BY behaves like the DISTINCT keyword:

The GROUP BY clause will be more useful when you use it with an aggregate function.

For example, the following statement uses the GROUP BY clause with the COUNT function to count the number of employees by department:

How it works.

• First, the GROUP BY clause groups the rows in the employees table by department id.
• Second, the COUNT(employee_id) returns the number of employee id values in each group.

SQL GROUP BY with INNER JOIN example

The following example returns the number of employees by department. And it uses an INNER JOIN clause to include the department name in the result:

SQL GROUP BY with ORDER BY example

The following example uses an ORDER BY clause to sort the departments by headcount:

Note that you can use either the headcount alias or the COUNT(employee_id) in the ORDER BY clause.

SQL GROUP BY with HAVING example

The following example uses the HAVING clause to find departments with headcounts are greater than 5:

SQL GROUP BY with MIN, MAX, and AVG example

The following query returns the minimum , maximum , and average salary of employees in each department.

SQL GROUP BY with SUM function example

To get the total salary per department, you apply the SUM function to the salary column and group employees by the department_id column as follows:

SQL GROUP BY multiple columns

So far, you have seen that we have grouped all employees by one column. For example, the following clause places all rows with the same values in the department_id column in one group.

How about grouping employees by values in both department_id and job_id columns?

This clause will group all employees with the same values in both department_id and job_id columns in one group.

The following statement groups rows with the same values in both department_id and job_id columns in the same group then return the rows for each of these groups.

• The GROUP BY clause groups the rows into groups based on the values of one or more columns.
• Use an aggregate function with the GROUP BY clause to calculate the summarized value for each group.

• SQL Cheat Sheet
• SQL Interview Questions
• MySQL Interview Questions
• PL/SQL Interview Questions
• Learn SQL and Database
• SQL Tutorial
• What is Database?
• Types of Databases
• Introduction of DBMS (Database Management System) - Set 1
• Non-Relational Databases and Their Types
• What is SQL?
• SQL Data Types

SQL Operators

• SQL Commands | DDL, DQL, DML, DCL and TCL Commands

Create Database in SQL

• SQL CREATE DATABASE
• SQL DROP DATABASE
• SQL Query to Rename Database
• SQL Select Database

Tables in SQL

• SQL CREATE TABLE
• SQL DROP TABLE
• SQL DELETE Statement
• ALTER (RENAME) in SQL
• DROP and TRUNCATE in SQL
• SQL Query to Copy, Duplicate or Backup Table
• What is Temporary Table in SQL?
• SQL ALTER TABLE

SQL Queries

• SQL SELECT Query
• SQL TOP, LIMIT, FETCH FIRST Clause
• SQL SELECT FIRST
• SQL - SELECT LAST
• SQL - SELECT RANDOM
• SQL SELECT IN Statement
• SQL - SELECT DATE
• SQL Query to Insert Multiple Rows
• SQL INSERT INTO Statement
• SQL UPDATE Statement
• SQL Query to Delete Duplicate Rows

SQL Clauses

• SQL | WHERE Clause
• SQL | WITH Clause
• SQL HAVING Clause with Examples
• SQL ORDER BY

SQL | GROUP BY

• SQL LIMIT Clause
• SQL AND and OR Operators
• SQL LIKE Operator
• SQL IN Operator
• SQL NOT Operator
• SQL NOT EQUAL Operator
• SQL IS NULL
• SQL UNION Operator
• SQL UNION ALL
• SQL | Except Clause
• SQL BETWEEN Operator
• SQL | ALL and ANY
• SQL | INTERSECT Clause
• SQL | EXISTS
• SQL CASE Statement

SQL Aggregate Functions

• SQL Aggregate functions
• SQL COUNT(), AVG() and SUM() Function
• SQL MIN() and MAX() Functions

SQL Data Constraints

• SQL NOT NULL Constraint
• SQL | UNIQUE Constraint
• SQL PRIMARY KEY Constraint
• SQL FOREIGN KEY Constraint
• Composite Key in SQL
• SQL - ALTERNATE KEY
• SQL | CHECK Constraint
• SQL | DEFAULT Constraint

SQL Joining Data

• SQL Joins (Inner, Left, Right and Full Join)
• SQL Outer Join
• SQL LEFT JOIN
• SQL RIGHT JOIN
• SQL FULL JOIN
• SQL CROSS JOIN
• SQL Self Join
• SQL | UPDATE with JOIN
• SQL DELETE JOIN
• Recursive Join in SQL

SQL Functions

• SQL Date and Time Functions
• SQL | String functions
• SQL | Numeric Functions
• SQL - Statistical Functions
• Working With JSON in SQL
• Conversion Function in SQL
• SQL LTRIM() Function
• SQL UPPER() Function
• SQL RTRIM() Function
• SQL Indexes
• SQL CREATE INDEX Statement
• SQL DROP INDEX Statement
• Difference Between Clustered and Non-Clustered Index

SQL Miscellaneous Topics

• SQL | Wildcard operators
• SQL Comments
• Pivot and Unpivot in SQL
• SQL Injection
• SQL Performance Tuning
• SQL Stored Procedures
• SQL TRANSACTIONS
• SQL | Subquery
• SQL | SEQUENCES
• SQL Auto Increment
• Window functions in SQL
• How to Get Current Date and Time in SQL?
• What is Cursor in SQL ?
• Dynamic SQL

The GROUP BY Statement in SQL is used to arrange identical data into groups with the help of some functions. i.e. if a particular column has the same values in different rows then it will arrange these rows in a group. 

• GROUP BY clause is used with the SELECT statement.
• In the query, the GROUP BY clause is placed after the WHERE clause.
• In the query, the GROUP BY clause is placed before the ORDER BY clause if used.
• In the query, the Group BY clause is placed before the Having clause.
• Place condition in the having clause .

SELECT column1, function_name(column2) FROM table_name WHERE condition GROUP BY column1, column2 ORDER BY column1, column2;

Explanation:

• function_name : Name of the function used for example, SUM() , AVG().
• table_name : Name of the table.
• condition : Condition used.

Let’s assume that we have two tables Employee and Student Sample Table is as follows after adding two tables we will do some specific operations to learn about GROUP BY.

Employee Table:

Insert some random data into a table and then we will perform some operations in GROUP BY.

Student Table:

Student TABLE

Group By single column

Group By single column means, placing all the rows with the same value of only that particular column in one group. Consider the query as shown below:

The above query will produce the below output: 

As you can see in the above output, the rows with duplicate NAMEs are grouped under the same NAME and their corresponding SALARY is the sum of the SALARY of duplicate rows. The SUM() function of SQL is used here to calculate the sum. The NAMES that are added are Aarav, Divya and Gaurav.

Group By Multiple Columns

 Group by multiple columns is say, for example, GROUP BY column1, column2 . This means placing all the rows with the same values of columns column 1 and column 2 in one group. Consider the below query:

Output : As you can see in the above output the students with both the same SUBJECT and YEAR are placed in the same group. And those whose only SUBJECT is the same but not YEAR belong to different groups. So here we have grouped the table according to two columns or more than one column. The Grouped subject and years are (English,2) , (Mathematics,1) and (Science,3) . The above mentioned all groups and years are repeated twice .

HAVING Clause in GROUP BY Clause

We know that the WHERE clause is used to place conditions on columns but what if we want to place conditions on groups? This is where the HAVING clause comes into use. We can use the HAVING clause to place conditions to decide which group will be part of the final result set. Also, we can not use aggregate functions like SUM(), COUNT(), etc. with the WHERE clause. So we have to use the HAVING clause if we want to use any of these functions in the conditions. 

SELECT column1, function_name(column2) FROM table_name WHERE condition GROUP BY column1, column2 HAVING condition ORDER BY column1, column2;

As you can see in the above output only Anjali name not appears in the output because it has SALARY is less than 50000. So it removed from the output. So like this we can use the HAVING clause here to place this condition as the condition is required to be placed on groups not columns.

This article is contributed by Deepak Busa . If you like GeeksforGeeks and would like to contribute, you can also write an article using write.geeksforgeeks.org or mail your article to [email protected]. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please write comments if you find anything incorrect, or if you want to share more information about the topic discussed above.

Please Login to comment...

Similar reads, improve your coding skills with practice.

What kind of Experience do you want to share?

SQL Group By Tutorial: Count, Sum, Average, and Having Clauses Explained

The GROUP BY clause is a powerful but sometimes tricky statement to think about.

Even eight years later, every time I use a GROUP BY I have to stop and think about what it's actually doing.

In this article we'll look at how to construct a GROUP BY clause, what it does to your query, and how you can use it to perform aggregations and collect insights about your data.

Here's what we'll cover:

Setting up your database

• Setting up example data (creating sales)

How does a GROUP BY work?

Writing group by clauses, aggregations ( count , sum , avg ), working with multiple groups, using functions in the group by, filtering groups with having, aggregates with implicit grouping.

Before we can write our queries we need to setup our database.

For these examples we'll be using PostgreSQL, but the queries and concepts shown here will easily translate to any other modern database system (like MySQL, SQL Server, and so on).

To work with our PostgreSQL database, we can use psql —the interactive PostgreSQL command line program. If you have another database client that you enjoy working with that's fine too.

To begin, let's create our database. With PostgreSQL already installed, we can run the command createdb <database-name> at our terminal to create a new database. I called mine fcc :

Next let's start the interactive console by using the command psql , and connect to the database we just made using \c <database-name> :

Note: I've cleaned up the psql output in these examples to make it easier to read, so don't worry if the output shown here isn't exactly what you've seen in your terminal.

I encourage you to follow along with these examples and run these queries for yourself. You will learn and remember far more by working through these examples rather than just reading them.

Setting up the data (creating sales)

For our examples we'll use a table that stores the sales records of various products across different store locations.

We'll call this table sales , and it will be a simple representation of store sales: the location name, product name, price, and the time it was sold.

If we were building this table in a real application we'd set up foreign keys to other tables (like locations or products ). But for illustrating the GROUP BY concepts we'll just use simple TEXT columns.

Let's create the table and insert some sales data:

We have three locations: HQ , Downtown , and 1st Street.

We have two products, Coffee and Bagel , and we insert these sales with different sold_at values to represent the items being sold at different days and times.

There are some sales today, some yesterday, and some from the day before yesterday.

To illustrate how the GROUP BY clause works, let's first talk through an example.

Imagine we had a room full of people who were born in different countries.

If we wanted to find the average height of the people in the room per country, we would first ask these people to separate into groups based on their birth country.

Once they were separated into their groups we could then calculate the average height within that group.

This is how the GROUP BY clause works. First we define how we want to group the rows together—then we can perform calculations or aggregations on the groups.

Multiple groups

We can group the data into as many groups or sub-groups as we want.

For example, after asking people to separate into groups based on their birth countries, we could tell each of those groups of countries to separate further into groups based on their eye color.

By doing this, we have groups of people based on the combination of their birth country and their eye color.

Now we could find the average height within each of these smaller groups, and we'd have a more specific result: average height per country per eye color .

GROUP BY clauses are often used for situations where you can use the phrase per something or for each something :

• Average height per birth country
• Total number of people for each eye and hair color combination
• Total sales per product

A GROUP BY clause is very easy to write—we just use the keywords GROUP BY and then specify the field(s) we want to group by:

This simple query groups our sales data by the location column.

We've done the grouping—but what do we put in our SELECT ?

The obvious thing to select is our location —we're grouping by it so we at least want to see the name of the groups we made:

The result is our three locations:

If we look at our raw table data ( SELECT * FROM sales; ), we'll see that we have four rows with a location of HQ , two rows with a location of Downtown , and two rows with a location of 1st Street:

By grouping on the location column, our database takes these inputs rows and identifies the unique locations among them—these unique locations serve as our "groups."

But what about the other columns in our table?

If we try to select a column like product that we didn't group by...

...we run into this error:

The problem here is we've taken eight rows and squished or distilled them down to three.

We can't just return the rest of the columns like normal—we had eight rows, and now we have three.

What do we do with the remaining five rows of data? Which of the eight rows' data should be displayed on these three distinct location rows?

There's not a clear and definitive answer here.

To use the rest of our table data, we also have to distill the data from these remaining columns down into our three location groups.

This means that we have to aggregate or perform a calculation to produce some kind of summary information about our remaining data.

Once we've decided how to group our data, we can then perform aggregations on the remaining columns.

These are things like counting the number of rows per group, summing a particular value across the group, or averaging information within the group.

To start, let's find the number of sales per location.

Since each record in our sales table is one sale, the number of sales per location would be the number of rows within each location group.

To do this we'll use the aggregate function COUNT() to count the number of rows within each group:

We use COUNT(*) which counts all of the input rows for a group.

( COUNT() also works with expressions, but it has slightly different behavior.)

Here's how the database executes this query:

• FROM sales — First, retrieve all of the records from the sales table
• GROUP BY location — Next, determine the unique location groups
• SELECT ... — Finally, select the location name and the count of the number of rows in that group

We also give this count of rows an alias using AS number_of_sales to make the output more readable. It looks like this:

The 1st Street location has two sales, HQ has four, and Downtown has two.

Here we can see how we've taken the remaining column data from our eight independent rows and distilled them into useful summary information for each location: the number of sales.

In a similar way, instead of counting the number of rows in a group, we could sum information within the group—like the total amount of money earned from those locations.

To do this we'll use the SUM() function:

Instead of counting the number of rows in each group we sum the dollar amount of each sale, and this shows us the total revenue per location:

Average ( AVG )

Finding the average sale price per location just means swapping out the SUM() function for the AVG() function:

So far we've been working with just one group: location.

What if we wanted to sub-divide that group even further?

Similar to the "birth countries and eye color" scenario we started with, what if we wanted to find the number of sales per product per location?

To do this all we need to do is add the second grouping condition to our GROUP BY statement:

By adding a second column in our GROUP BY we further sub-divide our location groups into location groups per product.

Because we're now also grouping by the product column, we can now return it in our SELECT !

(I'm going to throw some ORDER BY clauses on these queries to make the output easier to read.)

Looking at the result of our new grouping, we can see our unique location/product combinations:

Now that we have our groups, what do we want to do with the rest of our column data?

Well, we can find the number of sales per product per location using the same aggregate functions as before:

As an Exercise For The Reader™: find the total revenue (sum) of each product per location.

Next, let's try to find the total number of sales per day .

If we follow a similar pattern as we did with our locations and group by our sold_at column...

...we might expect to have each group be each unique day—but instead we see this:

It looks like our data isn't grouped at all—we get each row back individually.

But, our data is actually grouped! The problem is each row's sold_at is a unique value—so every row gets its own group!

The GROUP BY is working correctly, but this is not the output we want.

The culprit is the unique hour/minute/second information of the timestamp.

Each of these timestamps differ by hours, minutes, or seconds—so they are each placed in their own group.

We need to convert each of these date and time values into just a date:

• 2020-09-01 08:42:33.085995 => 2020-09-01
• 2020-09-01 09:42:33.085995 => 2020-09-01

Converted to a date, all of the timestamps on the same day will return the same date value—and will therefore be placed into the same group.

To do this, we'll cast the sold_at timestamp value to a date:

In our GROUP BY clause we use ::DATE to truncate the timestamp portion down to the "day." This effectively chops off the hours/minutes/seconds of the timestamp and just returns the day.

In our SELECT , we also return this same expression and give it an alias to pretty up the output.

For the same reason we couldn't return product without grouping by it or performing some kind of aggregation on it, the database won't let us return just sold_at —everything in the SELECT must either be in the GROUP BY or some kind of aggregate on the resulting groups.

The result is the sales per day that we originally wanted to see:

Next let's look at how to filter our grouped rows.

To do this, let's try to find days where we had more than one sale.

Without grouping, we would normally filter our rows by using a WHERE clause. For example:

With our groups, we may want to do something like this to filter our groups based on the count of rows...

Unfortunately, this doesn't work and we receive this error:

ERROR:  aggregate functions are not allowed in WHERE

Aggregate functions are not allowed in the WHERE clause because the WHERE clause is evaluated before the GROUP BY clause—there aren't any groups yet to perform calculations on.

But, there is a type of clause that allows us to filter, perform aggregations, and it is evaluated after the GROUP BY clause: the HAVING clause.

The HAVING clause is like a WHERE clause for your groups.

To find days where we had more than one sale, we can add a HAVING clause that checks the count of rows in the group:

This HAVING clause filters out any rows where the count of rows in that group is not greater than one, and we see that in our result set:

Just for the sake of completeness, here's the order of execution for all parts of a SQL statement:

• FROM — Retrieve all of the rows from the FROM table
• JOIN — Perform any joins
• WHERE — Filter rows
• GROUP BY - Form groups
• HAVING - Filter groups
• SELECT - Select the data to return
• ORDER BY - Order the output rows
• LIMIT - Return a certain number of rows

The last topic we'll look at is aggregations that can be performed without a GROUP BY —or maybe better said they have an implicit grouping.

These aggregations are useful in scenarios where you want to find one particular aggregate from a table—like the total amount of revenue or the greatest or least value of a column.

For example, we could find the total revenue across all locations by just selecting the sum from the entire table:

So far we've done $19 of sales across all locations ( hooray! ).

Another useful thing we could query is the first or last of something.

For example, what is the date of our first sale?

To find this we just use the MIN() function:

(To find the date of the last sale just substitute MAX() for MIN() .)

Using MIN / MAX

While these simple queries can be useful as a standalone query, they're often parts of filters for larger queries.

For example, let's try to find the total sales for the last day that we had sales.

One way we could write that query would be like this:

This query works, but we've obviously hardcoded the date of 2020-09-01 .

09/01/2020 may be the last date we had a sale, but it's not always going to be that date. We need a dynamic solution.

This can be achieved by combining this query with the MAX() function in a subquery:

In our WHERE clause we find the largest date in our table using a subquery: SELECT MAX(sold_at::DATE) FROM sales .

Then, we use this max date as the value we filter the table on, and sum the price of each sale.

Implicit grouping

I say that these are implicit groupings because if we try to select an aggregate value with a non-aggregated column like this...

...we get our familiar error:

GROUP BY is a tool

As with many other topics in software development, GROUP BY is a tool.

There are many ways to write and re-write these queries using combinations of GROUP BY , aggregate functions, or other tools like DISTINCT , ORDER BY , and LIMIT .

Understanding and working with GROUP BY 's will take a little bit of practice, but once you have it down you'll find an entirely new batch of problems are now solvable to you!

If you liked this post, you can follow me on twitter where I talk about database things and how to succeed in a career as a developer.

Thanks for reading!

A simple web developer who likes helping others learn how to program.

If you read this far, thank the author to show them you care. Say Thanks

Learn to code for free. freeCodeCamp's open source curriculum has helped more than 40,000 people get jobs as developers. Get started

Learn SQL practically and Get Certified .

Popular tutorials, learn sql interactively.

Learn Python practically and Get Certified .

Popular Examples

• Introduction
• Getting Started with SQL
• Introduction to Databases and SQL

SQL SELECT(I)

• SQL AND, OR, and NOT Operators
• SQL SELECT DISTINCT
• SQL SELECT AS Alias
• SQL SELECT LIMIT, TOP, FETCH FIRST
• SQL IN and NOT IN Operators
• SQL BETWEEN Operator
• SQL IS NULL and IS NOT NULL
• SQL MAX() and MIN()
• SQL COUNT()
• SQL SUM() AND AVG()

SQL SELECT(II)

• SQL ORDER BY Clause

SQL GROUP BY

• SQL LIKE and NOT LIKE Operators
• SQL Wildcards
• SQL Subquery
• SQL CTE (Common Table Expressions)
• SQL ANY and ALL

SQL HAVING Clause

• SQL EXISTS Operator
• SQL INNER JOIN
• SQL LEFT JOIN

SQL RIGHT JOIN

• SQL FULL OUTER JOIN
• SQL CROSS JOIN
• SQL Self JOIN

SQL Database and Table

• SQL CREATE DATABASE Statement
• SQL CREATE TABLE
• SQL DROP DATABASE Statement
• SQL DROP TABLE Statement
• SQL ALTER TABLE Statement
• SQL BACKUP DATABASE Statement

SQL Insert, Update and Delete

• SQL INSERT INTO
• SQL SELECT INTO (Copy Table)
• SQL INSERT INTO SELECT Statement
• SQL DELETE and TRUNCATE
• SQL Constraints
• SQL NOT NULL Constraint
• SQL UNIQUE Constraint
• SQL PRIMARY KEY Constraint
• SQL FOREIGN KEY Constraint
• SQL CHECK Constraint
• SQL DEFAULT Constraint
• SQL CREATE INDEX
• SQL Composite Key

SQL Additional Topics

• SQL Comments
• SQL Data Types
• SQL Operators
• SQL Date and Time
• SQL JOIN Three Tables
• SQL SUBSTRING()
• SQL Commands
• SQL REPLACE()
• SQL Stored Procedures
• SQL Injection

SQL Tutorials

SQL SUM With GROUP BY

SQL COUNT() With DISTINCT

In SQL, we use the GROUP BY clause to group rows based on the value of columns.

• SQL GROUP BY Syntax
• column1, column2 ... are the columns of the table
• table is the name of the table
• columnA, columnB ... are the column(s) based on which the rows will be grouped
• Example: GROUP BY Number of Customers in Each Country

Here, the SQL command groups the rows by the country column and counts the number of each country (because of the COUNT() function).

Note: The GROUP BY clause is used in conjunction with aggregate functions such as MIN() and MAX() , SUM() and AVG() , COUNT() , etc.

Due to the use of the AS alias, the compiler displays the results of the COUNT() function in the number column. To learn more, visit SQL AS Alias .

• Example: GROUP BY Amount Spent By Each Customer

Here, the SQL command sums the amount after grouping rows by customer_id .

• SQL GROUP BY Clause With JOIN

We can also use the GROUP BY clause with the JOIN clause. For example,

Here, the SQL command joins the Customers and Orders tables and groups the result set by customer_id (a customer). This gives us the number of orders that are placed by each customer.

Visit SQL JOIN and SQL LEFT JOIN to read more about the JOIN and LEFT JOIN clauses in SQL.

More on SQL GROUP BY

GROUP BY can also be used to group rows based on multiple columns. For example,

Here, the SQL command groups all persons with similar country and state , and gives the minimum age of each group.

We can use the GROUP BY clause with the HAVING clause to filter the result set based on aggregate functions. For example,

Here, the SQL command:

• counts the number of rows by grouping them by country
• returns the result set if their count is greater than 1.
• SQL ORDER BY

Table of Contents

Sorry about that.

Related Tutorials

Programming

SQL Group By Statements: Easy Examples and a Fun Tutorial

Nobody likes to feel lonely. Not even your SQL table data. Fortunately, a SQL GROUP BY clause can bring it all together—so your data never feels alone.

GROUP BY clauses are a powerful way to organize your SQL data into useful statistics and business insights. This fun tutorial will teach you what GROUP BY clauses are—and how to use them—via   simple ,   easy-to-understand   examples.

What’s a SQL GROUP BY Clause?

SQL GROUP BY clauses group together rows of table data that have the same information in a specific column. Let’s say you have a table column “country name” and another column “continent name." If you GROUP BY the “continent name” column, you can distill the table down to a list of individual continent names.

The GROUP BY clause in the SELECT statement indicates which column you want to GROUP BY. After that, the query returns a list of the unique entries for the indicated column. You can SELECT additional columns to display in the query in addition to the column indicated in the GROUP BY clause, but you’ll have to add up (or aggregate) the additional columns with SUM/COUNT commands.

If any of that wasn’t clear, don’t worry. The following examples will help you understand.

How to Use a GROUP BY Clause

It’s easier to understand GROUP BY clauses by visualizing one in action, so let’s expand the previous example table. Imagine a table named “world” with country statistics organized into the following columns: “name,” “continent,” “area,” “population,” and “gdp.”

The following examples will show you how to query this table with progressively more complicated SELECT statements.

1. SELECT + GROUP BY

Imagine you want to aggregate or group above table into continent data alone. You’ll write a SELECT statement that GROUPs BY the “continent” column like this:

This query produces the following result:

Easy-peasy, right?

2. SELECT + GROUP BY + COUNT/SUM

After GROUPing BY, if you want to know more about these continents, you’ll need to SELECT more columns of data. However, since you have grouped together (or aggregated) a lot of data into each continent row, you’ll need to add up whatever's inside before you can display the information. You’ll do that with SUM and COUNT functions.

If the column is an integer (number) value, you’ll use SUM to add up the information. If the column is an alphabetical value, you’ll use COUNT to add up the information. If you don’t use the SUM/COUNT functions when selecting additional columns, the query will come back with an error.

Here’s what your SELECT statement looks like when SELECTing table columns in addition to the one you GROUPed by. See how we used COUNT for alphabetical columns and SUM for numerical columns?

Take a look at the above result and notice the following:

• GROUP BY:   We grouped the “world” table by the “continent” column.
• COUNT:   We counted the different country “names” in each continent group and listed the total number.
• SUM:   We summed the numbers for “area,” “population,” and “gdp” found in each group and listed the total figure.

3. SELECT + GROUP BY + COUNT/SUM + HAVING

We can add another layer of distillation to this query with a HAVING clause. The HAVING clause lets you pull specific information out of the aggregated data. Since this additional refinement happens   after   GROUPing the data, HAVING clauses always come   after   the GROUP BY clause.

Here, we add a HAVING clause to pull out data for only continents with total populations over 150 million:

Look at that! Your data is getting cozier, and less lonely by the second!

4. SELECT + GROUP BY + COUNT/SUM + HAVING + ORDER BY

Since we’re focusing on population data, wouldn’t it be nice to ORDER BY the SUM(population)? We can organize the result by population (from least to greatest) by adding an ORDER BY clause to the query.

Check it out:

Here’s the result:

*If you want to ORDER BY a descending order, just put DESC after the clause like this: ORDER BY SUM(population) DESC.

5. SELECT + GROUP BY + COUNT/SUM + ORDER BY + WHERE

To keep the above examples simple, we didn’t include a WHERE clause yet, so let's try one now. We'll use a WHERE clause to SELECT specific data from the table.   After that,   we'll apply the GROUP BY function to the data selected in the WHERE clause.

Let's say that another way:   We’re going to isolate specific rows of the table with the WHERE clause, then we're going to group the information with a GROUP BY clause.

The following query lists information for countries that start with the letter A only. Next, it groups the resulting data together by continent. Then, it orders the list, beginning with the continents that have the most “A” countries first. Here’s how that query looks:

Can you see what we did? Look at the query and notice the following:

• The WHERE clause selects only table data for countries that begin with the letters “A” by using the wildcard operator ‘%.’
• The GROUP BY clause groups the rows together by continent.
• The COUNT and SUM functions serve to (1) count the number of “A” countries each continent has, and (2) sum up the area, population and gdp figures for the “A” countries by continent.
• The ORDER BY clause orders the list, beginning with the continents that have the most "A" countries first (i.e., in DESC order).

Here is the result:

A Final Note About GROUP BY Syntax Order

There’s one last thing we haven’t talked about: The syntax order of a GROUP BY clause within a SELECT statement. When we added additional clauses to the SELECT statements above, you might have noticed that we didn't simply tack them onto the end. Clauses in SELECT statements   must   follow a specific syntax order. Otherwise, the query won’t work.

Here’s there order that clauses need to appear in:

• SELECT (SUM/COUNT functions go here)

The SQL GROUP BY clause always   follows   the WHERE clause and   precedes   the HAVING and ORDER BY clauses. You might not use all of the above clauses (depending on the type of query you’re writing). However, the ones you use   must   appear in this order.

Congratulations: Your Data Will Never Be Lonely Again

*SELECT cuddly FROM animals GROUP BY cuddly

Now that you understand how to use a GROUP BY clause, your data will never be lonely again. If you want practice your new SKILLZ. You can play around with the "world" table on the   SQLZoo website . Try cutting-and-pasting the above examples into the query field and press "submit." Then you can GROUP BY whatever kind of data you want!

Have fun! And don't forget to   read about my experience learning SQL with SQLZoo.net here .

Try SQLBot: It's Totally Free!

Writing SQL queries to produce awe-inspiring insights is one thing. Remembering to submit the query and push the results to your team on a daily, weekly, or monthly basis is another. With SQLBot, you'll never forget again. SQLBot submits your queries on autopilot! Then it sends the results to you and your team by SlackDaily—hourly, daily, weekly, whenever you want.

If you want to give SQLBot a try,  click this link and create a free account now .

About the Author

Fascinated by emerging technologies, data science, SQL, and the laws and market trends that follow them,  Jeremy Hillpot  is a  freelance technology writer  whose background in consumer fraud litigation provides a unique perspective on new technologies in the fields of data science, investments, tech, cryptocurrency, and the law.

• Why We Built SQLBot
• Privacy Policy

Social/News

Sql editor reviews.

• Squirrel SQL

SQL - GROUP BY Clause

The GROUP BY clause is used to get the summary data based on one or more groups. The groups can be formed on one or more columns. For example, the GROUP BY query will be used to count the number of employees in each department, or to get the department wise total salaries.

You must use the aggregate functions such as COUNT() , MAX() , MIN() , SUM() , AVG() , etc., in the SELECT query. The result of the GROUP BY clause returns a single row for each value of the GROUP BY column.

The SELECT clause can include columns that are used with the GROUP BY clause. So, to include other columns in the SELECT clause, use the aggregate functions like COUNT() , MAX() , MIN() , SUM() , AVG() with those columns.

• The GROUP BY clause is used to form the groups of records.
• The GROUP BY clause must come after the WHERE clause if present and before the HAVING clause.
• The GROUP BY clause can include one or more columns to form one or more groups based on that columns.
• Only the GROUP BY columns can be included in the SELECT clause. To use other columns in the SELECT clause, use the aggregate functions with them.

For the demo purpose, we will use the following Employee and Department tables in all examples.

Consider the following GROUP BY query.

The above query includes the GROUP BY DeptId clause, so you can include only DeptId in the SELECT clause. You need to use aggregate functions to include other columns in the SELECT clause, so COUNT(EmpId) is included because we want to count the number of employees in the same DeptId . The 'No of Employees' is an alias of the COUNT(EmpId) column. The query will display the following result.

The following query gets the department name instead of DeptId in the result.

In the same way, the following query gets the department-wise total salaries.

The following query would throw an error, because dept.Name is not included in the GROUP BY clause, or no aggregate function is used.

We are a team of passionate developers, educators, and technology enthusiasts who, with their combined expertise and experience, create in -depth, comprehensive, and easy to understand tutorials.We focus on a blend of theoretical explanations and practical examples to encourages hands - on learning. Visit About Us page for more information.

.NET Tutorials

Database tutorials, javascript tutorials, programming tutorials.

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

SELECT - GROUP BY- Transact-SQL

• 18 contributors

A SELECT statement clause that divides the query result into groups of rows, usually by performing one or more aggregations on each group. The SELECT statement returns one row per group.

To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation .

column-expression

Specifies a column or a non-aggregate calculation on a column. This column can belong to a table, derived table, or view. The column must appear in the FROM clause of the SELECT statement, but is not required to appear in the SELECT list.

For valid expressions, see expression .

The column must appear in the FROM clause of the SELECT statement, but is not required to appear in the SELECT list. However, each table or view column in any nonaggregate expression in the <select> list must be included in the GROUP BY list:

The following statements are allowed:

The following statements are not allowed:

The column expression cannot contain:

• A column alias that is defined in the SELECT list. It can use a column alias for a derived table that is defined in the FROM clause.
• A column of type text , ntext , or image . However, you can use a column of text, ntext, or image as an argument to a function that returns a value of a valid data type. For example, the expression can use SUBSTRING() and CAST(). This also applies to expressions in the HAVING clause.
• xml data type methods. It can include a user-defined function that uses xml data type methods. It can include a computed column that uses xml data type methods.
• A subquery. Error 144 is returned.
• A column from an indexed view.

GROUP BY column-expression [ ,...n ]

Groups the SELECT statement results according to the values in a list of one or more column expressions.

For example, this query creates a Sales table with columns for Country, Region, and Sales. It inserts four rows and two of the rows have matching values for Country and Region.

The Sales table contains these rows:

This next query groups Country and Region and returns the aggregate sum for each combination of values.

The query result has 3 rows since there are 3 combinations of values for Country and Region. The TotalSales for Canada and British Columbia is the sum of two rows.

GROUP BY ROLLUP

Creates a group for each combination of column expressions. In addition, it "rolls up" the results into subtotals and grand totals. To do this, it moves from right to left decreasing the number of column expressions over which it creates groups and the aggregation(s).

The column order affects the ROLLUP output and can affect the number of rows in the result set.

For example, GROUP BY ROLLUP (col1, col2, col3, col4) creates groups for each combination of column expressions in the following lists.

• col1, col2, col3, col4
• col1, col2, col3, NULL
• col1, col2, NULL, NULL
• col1, NULL, NULL, NULL
• NULL, NULL, NULL, NULL --This is the grand total

Using the table from the previous example, this code runs a GROUP BY ROLLUP operation instead of a simple GROUP BY.

The query result has the same aggregations as the simple GROUP BY without the ROLLUP. In addition, it creates subtotals for each value of Country. Finally, it gives a grand total for all rows. The result looks like this:

GROUP BY CUBE ( )

GROUP BY CUBE creates groups for all possible combinations of columns. For GROUP BY CUBE (a, b) the results has groups for unique values of (a, b), (NULL, b), (a, NULL), and (NULL, NULL).

Using the table from the previous examples, this code runs a GROUP BY CUBE operation on Country and Region.

The query result has groups for unique values of (Country, Region), (NULL, Region), (Country, NULL), and (NULL, NULL). The results look like this:

GROUP BY GROUPING SETS ( )

The GROUPING SETS option gives you the ability to combine multiple GROUP BY clauses into one GROUP BY clause. The results are the equivalent of UNION ALL of the specified groups.

For example, GROUP BY ROLLUP (Country, Region) and GROUP BY GROUPING SETS ( ROLLUP (Country, Region) ) return the same results.

When GROUPING SETS has two or more elements, the results are a union of the elements. This example returns the union of the ROLLUP and CUBE results for Country and Region.

The results are the same as this query that returns a union of the two GROUP BY statements.

SQL does not consolidate duplicate groups generated for a GROUPING SETS list. For example, in GROUP BY ( (), CUBE (Country, Region) ) , both elements return a row for the grand total and both rows will be listed in the results.

GROUP BY ()

Specifies the empty group, which generates the grand total. This is useful as one of the elements of a GROUPING SET. For example, this statement gives the total sales for each country/region and then gives the grand-total for all countries/regions.

GROUP BY ALL column-expression [ ,...n ]

Applies to: SQL Server and Azure SQL Database

This syntax is provided for backward compatibility only. It will be removed in a future version. Avoid using this syntax in new development work, and plan to modify applications that currently use this syntax.

Specifies to include all groups in the results regardless of whether they meet the search criteria in the WHERE clause. Groups that don't meet the search criteria have NULL for the aggregation.

GROUP BY ALL:

• Is not supported in queries that access remote tables if there is also a WHERE clause in the query.
• Will fail on columns that have the FILESTREAM attribute.

GROUP BY column-expression [ ,...n ] WITH { CUBE | ROLLUP }

This syntax is provided for backward compatibility only. Avoid using this syntax in new development work, and plan to modify applications that currently use this syntax.

WITH (DISTRIBUTED_AGG)

Applies to: Azure Synapse Analytics and Analytics Platform System (PDW)

The DISTRIBUTED_AGG query hint forces the massively parallel processing (MPP) system to redistribute a table on a specific column before performing an aggregation. Only one column in the GROUP BY clause can have a DISTRIBUTED_AGG query hint. After the query finishes, the redistributed table is dropped. The original table is not changed.

NOTE: The DISTRIBUTED_AGG query hint is provided for backwards compatibility with earlier Analytics Platform System (PDW) versions and will not improve performance for most queries. By default, MPP already redistributes data as necessary to improve performance for aggregations.

General Remarks

How group by interacts with the select statement.

SELECT list:

• Vector aggregates. If aggregate functions are included in the SELECT list, GROUP BY calculates a summary value for each group. These are known as vector aggregates.
• Distinct aggregates. The aggregates AVG (DISTINCT column_name ), COUNT (DISTINCT column_name ), and SUM (DISTINCT column_name ) are supported with ROLLUP, CUBE, and GROUPING SETS.

WHERE clause:

• SQL removes Rows that do not meet the conditions in the WHERE clause before any grouping operation is performed.

HAVING clause:

• SQL uses the having clause to filter groups in the result set.

ORDER BY clause:

• Use the ORDER BY clause to order the result set. The GROUP BY clause does not order the result set.

NULL values:

• If a grouping column contains NULL values, all NULL values are considered equal, and they are collected into a single group.

Limitations and Restrictions

Applies to: SQL Server (starting with 2008) and Azure Synapse Analytics

Maximum capacity

For a GROUP BY clause that uses ROLLUP, CUBE, or GROUPING SETS, the maximum number of expressions is 32. The maximum number of groups is 4096 (2 12 ). The following examples fail because the GROUP BY clause has more than 4096 groups.

The following example generates 4097 (2 12 + 1) grouping sets and will fail.

The following example generates 4097 (2 12 + 1) groups and will fail. Both CUBE () and the () grouping set produce a grand total row and duplicate grouping sets aren't eliminated.

This example uses the backwards compatible syntax. It generates 8192 (2 13 ) grouping sets and will fail.

For backwards compatible GROUP BY clauses that don't contain CUBE or ROLLUP, the number of group by items is limited by the GROUP BY column sizes, the aggregated columns, and the aggregate values involved in the query. This limit originates from the limit of 8,060 bytes on the intermediate worktable that is needed to hold intermediate query results. A maximum of 12 grouping expressions is permitted when CUBE or ROLLUP is specified.

Support for ISO and ANSI SQL-2006 GROUP BY Features

The GROUP BY clause supports all GROUP BY features that are included in the SQL-2006 standard with the following syntax exceptions:

Grouping sets aren't allowed in the GROUP BY clause unless they are part of an explicit GROUPING SETS list. For example, GROUP BY Column1, (Column2, ...ColumnN ) is allowed in the standard but not in Transact-SQL. Transact-SQL supports GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)) and GROUP BY Column1, Column2, ... ColumnN , which are semantically equivalent. These are semantically equivalent to the previous GROUP BY example. This is to avoid the possibility that GROUP BY Column1, (Column2, ...ColumnN ) might be misinterpreted as GROUP BY C1, GROUPING SETS ((Column2, ...ColumnN)) , which aren't semantically equivalent.

Grouping sets aren't allowed inside grouping sets. For example, GROUP BY GROUPING SETS (A1, A2,...An, GROUPING SETS (C1, C2, ...Cn)) is allowed in the SQL-2006 standard but not in Transact-SQL. Transact-SQL allows GROUP BY GROUPING SETS( A1, A2,...An, C1, C2, ...Cn ) or GROUP BY GROUPING SETS( (A1), (A2), ... (An), (C1), (C2), ... (Cn) ) , which are semantically equivalent to the first GROUP BY example and have a more clear syntax.

GROUP BY [ALL/DISTINCT] is only allowed in a simple GROUP BY clause that contains column expressions. It isn't allowed with the GROUPING SETS, ROLLUP, CUBE, WITH CUBE or WITH ROLLUP constructs. ALL is the default and is implicit. It is also only allowed in the backwards compatible syntax.

Comparison of Supported GROUP BY Features

The following table describes the GROUP BY features that are supported based upon SQL versions and database compatibility level.

A. Use a simple GROUP BY clause

The following example retrieves the total for each SalesOrderID from the SalesOrderDetail table. This example uses AdventureWorks.

B. Use a GROUP BY clause with multiple tables

The following example retrieves the number of employees for each City from the Address table joined to the EmployeeAddress table. This example uses AdventureWorks.

C. Use a GROUP BY clause with an expression

The following example retrieves the total sales for each year by using the DATEPART function. The same expression must be present in both the SELECT list and GROUP BY clause.

D. Use a GROUP BY clause with a HAVING clause

The following example uses the HAVING clause to specify which of the groups generated in the GROUP BY clause should be included in the result set.

Examples: Azure Synapse Analytics and Parallel Data Warehouse

E. basic use of the group by clause.

The following example finds the total amount for all sales on each day. One row containing the sum of all sales is returned for each day.

F. Basic use of the DISTRIBUTED_AGG hint

This example uses the DISTRIBUTED_AGG query hint to force the appliance to shuffle the table on the CustomerKey column before performing the aggregation.

G. Syntax Variations for GROUP BY

When the select list has no aggregations, each column in the select list must be included in the GROUP BY list. Computed columns in the select list can be listed, but are not required, in the GROUP BY list. These are examples of syntactically valid SELECT statements:

H. Using a GROUP BY with multiple GROUP BY expressions

The following example groups results using multiple GROUP BY criteria. If, within each OrderDateKey group, there are subgroups that can be differentiated by DueDateKey , a new grouping will be defined for the result set.

I. Using a GROUP BY clause with a HAVING clause

The following example uses the HAVING clause to specify the groups generated in the GROUP BY clause that should be included in the result set. Only those groups with order dates in 2004 or later will be included in the results.

GROUPING_ID (Transact-SQL) GROUPING (Transact-SQL) SELECT (Transact-SQL) SELECT Clause (Transact-SQL)

Was this page helpful?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

Home » SQL Server GROUP BY

SQL Server GROUP BY

Summary : in this tutorial, you will learn how to use the SQL Server GROUP BY clause to arrange rows in groups by one or more columns.

Introduction to SQL Server GROUP BY clause

The GROUP BY clause allows you to arrange the rows of a query in groups. The groups are determined by the columns that you specify in the GROUP BY clause.

The following illustrates the GROUP BY clause syntax:

In this query, the GROUP BY clause produced a group for each combination of the values in the columns listed in the GROUP BY clause.

Consider the following example:

In this example, we retrieved the customer id and the ordered year of the customers with customer id one and two.

As you can see clearly from the output, the customer with the id one placed one order in 2016 and two orders in 2018. The customer with id 2 placed two orders in 2017 and one order in 2018.

Let’s add a GROUP BY clause to the query to see the effect:

The GROUP BY clause arranged the first three rows into two groups and the next three rows into the other two groups with the unique combinations of the customer id and order year.

Functionally speaking, the GROUP BY clause in the above query produced the same result as the following query that uses the DISTINCT clause:

SQL Server GROUP BY clause and aggregate functions

In practice, the GROUP BY clause is often used with aggregate functions for generating summary reports.

An aggregate function performs a calculation on a group and returns a unique value per group. For example, COUNT() returns the number of rows in each group. Other commonly used aggregate functions are SUM() , AVG() (average), MIN() (minimum), MAX() (maximum).

The GROUP BY clause arranges rows into groups and an aggregate function returns the summary (count, min, max, average, sum, etc.,) for each group.

For example, the following query returns the number of orders placed by the customer by year:

If you want to reference a column or expression that is not listed in the GROUP BY clause, you must use that column as the input of an aggregate function . Otherwise, you will get an error because there is no guarantee that the column or expression will return a single value per group. For example, the following query will fail:

More GROUP BY clause examples

Let’s take some more examples to understand how the GROUP BY clause works.

Using GROUP BY clause with the COUNT() function example

The following query returns the number of customers in every city:

In this example, the GROUP BY clause groups the customers by city and the COUNT()  function returns the number of customers in each city.

Similarly, the following query returns the number of customers by state and city.

Using GROUP BY clause with the MIN and MAX functions example

The following statement returns the minimum and maximum list prices of all products with the model 2018 by brand:

In this example, the WHERE clause is processed before the GROUP BY clause, as always.

Using GROUP BY clause with the  AVG() function example

The following statement uses the AVG()  function to return the average list price by brand for all products with the model year 2018:

Using GROUP BY clause with SUM function example

See the following order_items table:

The following query uses the SUM() function to get the net value of every order:

In this tutorial, you have learned how to use the SQL Server GROUP BY clause to arrange rows in groups by a specified list of columns.

Advertisements

• Oracle / PLSQL
• Web Development
• Color Picker
• Programming
• Techie Humor

Tutorial Resources

• AND & OR
• COMPARISON OPERATORS
• IS NOT NULL
• SELECT LIMIT

SQL Advanced

• ALTER TABLE
• CREATE TABLE
• CREATE TABLE AS
• GLOBAL TEMP
• PRIMARY KEY

SQL Functions

SQL: GROUP BY Clause

This SQL tutorial explains how to use the SQL GROUP BY clause with syntax and examples.

Description

The SQL GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.

The syntax for the GROUP BY clause in SQL is:

Parameters or Arguments

Ddl/dml for examples.

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example - Using GROUP BY with the SUM Function

Let's look at how to use the GROUP BY clause with the SUM function in SQL.

In this example, we have a table called employees with the following data:

Enter the following SQL statement:

There will be 2 records selected. These are the results that you should see:

In this example, we've used the SUM function to add up all of the salaries for each dept_id and we've aliased the results of the SUM function as total_salaries . Because the dept_id is not encapsulated in the SUM function, it must be listed in the GROUP BY clause.

Example - Using GROUP BY with the COUNT function

Let's look at how to use the GROUP BY clause with the COUNT function in SQL.

In this example, we have a table called products with the following data:

There will be 3 records selected. These are the results that you should see:

In this example, we've used the COUNT function to calculate the number of products for each category_id and we've aliased the results of the COUNT function as total_products . We've excluded any category_id values that are NULL by filtering them out in the WHERE clause. Because the category_id is not encapsulated in the COUNT function, it must be listed in the GROUP BY clause.

Example - Using GROUP BY with the MIN function

Let's next look at how to use the GROUP BY clause with the MIN function in SQL.

In this example, we will use the employees table again that is populated the following data:

In this example, we've used the MIN function to return the lowest salary for each dept_id and we've aliased the results of the MIN function as lowest_salary . Because the dept_id is not encapsulated in the MIN function, it must be listed in the GROUP BY clause.

Example - Using GROUP BY with the MAX function

Finally, let's look at how to use the GROUP BY clause with the MAX function .

Let's use the employees table again, but this time find the highest salary for each dept_id :

In this example, we've used the MAX function to return the highest salary for each dept_id and we've aliased the results of the MAX function as highest_salary . The dept_id column must be listed in the GROUP BY clause because it is not encapsulated in the MAX function.

Home | About Us | Contact Us | Testimonials | Donate

While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy .

Copyright © 2003-2024 TechOnTheNet.com. All rights reserved.

• SQL Data Types
• Codd's 12-Rule Relational Database Definition
• The Components of a Table
• Create/Alter/Drop Schema
• Create/Alter Database
• Create Table
• Primary Key
• Foreign Key
• Constraints
• Alter Table
• Basic Select Statement
• Where Clause
• SQL Operators
• SQL Functions
• Insert Statement
• Update Statement
• Delete Statement
• Create Alter View
• Update View
• Create Alter Drop Index
• SQL Procedure - Create, Alter, Drop
• Controlling Transactions
• Database Security
• Advanced SQL Topics
• SQL Dual table
• SQL Injection
• SQL Question Answer

SQL GROUP BY clause

Group by clause.

The usage of SQL GROUP BY clause is, to divide the rows in a table into smaller groups.

The GROUP BY clause is used with the SQL SELECT statement.

The grouping can happen after retrieves the rows from a table.

When some rows are retrieved from a grouped result against some condition, that is possible with HAVING clause.

The GROUP BY clause is used with the SELECT statement to make a group of rows based on the values of a specific column or expression. The SQL AGGREGATE function can be used to get summary information for every group and these are applied to an individual group.

The WHERE clause is used to retrieve rows based on a certain condition, but it can not be applied to grouped result.

In an SQL statement, suppose you are using GROUP BY, if required you can use HAVING instead of WHERE, after GROUP BY.

Parameters:

Pictorial Presentation of Groups of Data

Some important questions related to the SQL GROUP BY clause:

What is the purpose of the SQL GROUP BY clause?

• The GROUP BY clause is used to group rows that have the same values into summary rows, such as finding the total sales for each product category.

Can you use multiple columns in the GROUP BY clause?

• Yes, you can group rows by multiple columns by listing them in the GROUP BY clause. This allows for more detailed grouping and analysis.

What is the difference between WHERE and HAVING clauses when used with GROUP BY?

• The WHERE clause filters rows before grouping, while the HAVING clause filters groups after grouping. HAVING is used with aggregate functions to filter groups based on specific conditions.

Can you sort the result set using columns that are not included in the GROUP BY clause?

• It depends on the database system. Some systems allow sorting by non-grouped columns, while others require all selected columns to be included in the GROUP BY clause.

What happens if you use a column in the SELECT list that is not included in the GROUP BY clause?

• It depends on the database system. In some systems, it throws an error, while in others, it executes the query but might return unexpected results.

Can you use aliases in the GROUP BY clause?

• Some database systems allow using aliases in the GROUP BY clause, typically if they are defined in the same SELECT statement. However, it's best practice to use the actual column names.

What is the order of execution for clauses in a SQL query with GROUP BY?

• The order of execution is: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. This means that grouping occurs before selecting and filtering, and after joining tables and applying conditions.

How do you include aggregated values in the result set along with grouped columns?

• You can include aggregated values in the SELECT list along with grouped columns. However, if an aggregate function is used without the GROUP BY clause, it operates on the entire result set.

Can you use aggregate functions without the GROUP BY clause?

• Yes, you can use aggregate functions like SUM(), COUNT(), AVG(), etc., without the GROUP BY clause. In such cases, the aggregate function operates on the entire result set.

What are some common mistakes to avoid when using the GROUP BY clause?

• Forgetting to include all non-aggregated columns in the GROUP BY clause, misusing the HAVING clause, and misunderstanding the order of execution of clauses in a SQL query are some common mistakes to avoid when using the GROUP BY clause.

Using GROUP BY with Aggregate Functions

- The power of aggregate functions is greater when combined with the GROUP BY clause. - The GROUP BY clause is rarely used without an aggregate function.

SQL GROUP BY with COUNT() function

The following query displays number of employees work in each department.

Sample table: employees

Explanation:

• This SQL code is a SELECT statement that retrieves data from the 'employees' table.
• It calculates the number of employees in each department and displays the department code along with the count.
• The SELECT clause selects the 'department_id' column from the 'employees' table and renames it as 'Department Code' in the result set.
• The COUNT(*) function counts the number of rows for each department.
• The result column from the COUNT(*) function is renamed as 'No of Employees'.
• The GROUP BY clause groups the result set by the 'department_id' column, ensuring that the count is calculated for each unique department.
• The query does not filter or manipulate the data further; it simply retrieves the department code and the count of employees for each department from the 'employees' table.

Sample Output:

SQL GROUP BY with SUM() function

The following query displays total salary paid to employees work in each department.

• It calculates the total salary for each department and displays the department ID along with the sum of salaries.
• The SELECT clause selects the 'department_id' column from the 'employees' table.
• The SUM(salary) function calculates the sum of salaries for each department.
• The GROUP BY clause groups the result set by the 'department_id' column, ensuring that the sum is calculated for each unique department.
• The query does not filter or manipulate the data further; it simply retrieves the department ID and the sum of salaries for each department from the 'employees' table.

Relational Algebra Expression:

Relational Algebra Tree:

SQL GROUP BY with COUNT() and SUM() function

The following query displays number of employees, total salary paid to employees work in each department.

• It calculates the number of employees, total salary, and displays the department code for each department.
• The COUNT(*) function counts the number of rows for each department and renames the result column as 'No of Employees'.
• The SUM(salary) function calculates the sum of salaries for each department and renames the result column as 'Total Salary'.
• The GROUP BY clause groups the result set by the 'department_id' column, ensuring that calculations are performed for each unique department.
• The query does not filter or manipulate the data further; it simply retrieves the department code, the number of employees, and the total salary for each department from the 'employees' table.

SQL GROUP BY on more than one columns

The following query displays the department code, job id, total salary paid to employees group by department_id, job_id.

• It calculates the total salary for each combination of department and job and displays the department code, job ID, and total salary.
• It also selects the 'job_id' column to display the job ID in the result set.
• The SUM(salary) function calculates the sum of salaries for each combination of department and job.
• The GROUP BY clause groups the result set first by the 'department_id' column and then further by the 'job_id' column, ensuring that calculations are performed for each unique combination.
• The query retrieves the department code, job ID, and total salary for each combination of department and job from the 'employees' table.

SQL GROUP BY with WHERE clause

The following query displays the department code, total salary paid to employees group by department_id and manager_id=103.

• It calculates the total salary for each department where the manager ID is 103 and displays the department code and total salary.
• The SUM(salary) function calculates the sum of salaries for each department where the manager ID is 103.
• The WHERE clause filters the rows where the manager ID is 103.
• The query retrieves the department code and total salary for each department where the manager ID is 103 from the 'employees' table.

SQL GROUP BY with HAVING clause

The following query displays the department id, number of employees of those groups that have more than 2 employees:

• It counts the number of employees in each department and displays the department ID along with the count.
• The count(*) function counts the number of rows for each department.
• The HAVING clause filters the grouped results to include only departments with more than two employees.
• The query retrieves the department ID and the count of employees for each department from the 'employees' table, but only for departments with more than two employees.

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Follow us on Facebook and Twitter for latest update.

• Weekly Trends and Language Statistics

• SQL Tutorial
• SQL - Overview
• SQL - RDBMS Concepts
• SQL - Databases
• SQL - Syntax
• SQL - Data Types
• SQL - Operators
• SQL - Expressions
• SQL Database
• SQL - Create Database
• SQL - Drop Database
• SQL - Select Database
• SQL - Rename Database
• SQL - Show Databases
• SQL - Backup Database
• SQL - Create Table
• SQL - Show Tables
• SQL - Rename Table
• SQL - Truncate Table
• SQL - Clone Tables
• SQL - Temporary Tables
• SQL - Alter Tables
• SQL - Drop Table
• SQL - Delete Table
• SQL - Constraints
• SQL Queries
• SQL - Insert Query
• SQL - Select Query
• SQL - Select Into
• SQL - Insert Into Select
• SQL - Update Query
• SQL - Delete Query
• SQL - Sorting Results
• SQL - Create Views
• SQL - Update Views
• SQL - Drop Views
• SQL - Rename Views
• SQL Operators and Clauses
• SQL - Where Clause
• SQL - Top Clause
• SQL - Distinct Clause
• SQL - Order By Clause

SQL - Group By Clause

• SQL - Having Clause
• SQL - AND & OR
• SQL - BOOLEAN (BIT) Operator
• SQL - LIKE Operator
• SQL - IN Operator
• SQL - ANY, ALL Operators
• SQL - EXISTS Operator
• SQL - NOT Operator
• SQL - NOT EQUAL
• SQL - IS NULL
• SQL - IS NOT NULL
• SQL - NOT NULL
• SQL - BETWEEN Operator
• SQL - UNION Operator
• SQL - UNION vs UNION ALL
• SQL - INTERSECT Operator
• SQL - EXCEPT Operator
• SQL - Aliases
• SQL - Using Joins
• SQL - Inner Join
• SQL - Left Join
• SQL - Right Join
• SQL - Cross Join
• SQL - Full Join
• SQL - Self Join
• SQL - Delete Join
• SQL - Update Join
• SQL - Left Join vs Right Join
• SQL - Union vs Join
• SQL - Unique Key
• SQL - Primary Key
• SQL - Foreign Key
• SQL - Composite Key
• SQL - Alternate Key
• SQL Indexes
• SQL - Indexes
• SQL - Create Index
• SQL - Drop Index
• SQL - Show Indexes
• SQL - Unique Index
• SQL - Clustered Index
• SQL - Non-Clustered Index
• Advanced SQL
• SQL - Wildcards
• SQL - Comments
• SQL - Injection
• SQL - Hosting
• SQL - Min & Max
• SQL - Null Functions
• SQL - Check Constraint
• SQL - Default Constraint
• SQL - Stored Procedures
• SQL - NULL Values
• SQL - Transactions
• SQL - Sub Queries
• SQL - Handling Duplicates
• SQL - Using Sequences
• SQL - Auto Increment
• SQL - Date & Time
• SQL - Cursors
• SQL - Common Table Expression
• SQL - Group By vs Order By
• SQL - IN vs EXISTS
• SQL - Database Tuning
• SQL Function Reference
• SQL - Date Functions
• SQL - String Functions
• SQL - Aggregate Functions
• SQL - Numeric Functions
• SQL - Text & Image Functions
• SQL - Statistical Functions
• SQL - Logical Functions
• SQL - Cursor Functions
• SQL - JSON Functions
• SQL - Conversion Functions
• SQL - Datatype Functions
• SQL Useful Resources
• SQL - Questions and Answers
• SQL - Quick Guide
• SQL - Useful Functions
• SQL - Useful Resources
• SQL - Discussion
• Selected Reading
• UPSC IAS Exams Notes
• Developer's Best Practices
• Questions and Answers
• Effective Resume Writing
• HR Interview Questions
• Computer Glossary

The SQL GROUP BY Clause

Group by clause with aggregate functions, group by clause on single columns, group by clause with multiple columns, group by with order by clause, group by with having clause.

The SQL GROUP BY clause is used in conjunction with the SELECT statement to arrange identical data into groups. This clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY and HAVING clauses (if they exist).

The main purpose of grouping the records of a table based on particular columns is to perform calculations on these groups. Therefore, The GROUP BY clause is typically used with aggregate functions such as SUM(), COUNT(), AVG(), MAX(), or MIN() etc.

For example, if you have a table named SALES_DATA containing the sales data with the columns YEAR, PRODUCT, and SALES. To calculate the total sales in an year, the GROUP BY clause can be used to group the records in this table based on the year and calculate the sum of sales in each group using the SUM() function.

Following is the basic syntax of the SQL GROUP BY clause −

Where, column_name(s) refers to the name of one or more columns in the table that we want to group the data by and the table_name refers to the name of the table that we want to retrieve data from.

Typically, we group the record of a table to perform calculations on them. Therefore, the SQL GROUP BY clause is often used with the aggregate functions such as SUM(), AVG(), MIN(), MAX(), COUNT(), etc.

Assume we have created a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary, using the following query −

Now insert values into this table using the INSERT statement as follows −

The table created is as shown below −

The following SQL query groups the CUSTOMERS table based on AGE and counts the number of records in each group −

Following is the result produced −

In the following query, we are finding the highest salary for each age −

Following is the output of the above query −

Similarly we can group the records of the CUSTOMERS table based on the AGE column and calculate the maximum salary, average and sum of the SALARY values in each group using the MIN(), AVG() and SUM() functions respectively.

When we use the GROUP BY clause with a single column, all the rows in the table that have the same value in that particular column will be merged into a single record.

In the following example we are grouping the above created CUSTOMERS table by the ADDRESS column and calculating the average salary of the customer from each city −

This would produce the following result −

When we use the GROUP BY clause with multiple columns, all the rows in the table that have the same values in all of the specified columns will be merged into a single group.

In the following query we are grouping the records of the CUSTOMERS table based on the columns ADDRESS and AGE and −

We can use the ORDER BY clause with GROUP BY in SQL to sort the grouped data by one or more columns.

Following is the syntax for using ORDER BY clause with GROUP BY clause in SQL −

In here, we are finding the highest salary for each age, sorted by high to low −

We can also use the GROUP BY clause with the HAVING clause filter the grouped data in a table based on specific criteria.

Following is the syntax for using ORDER BY clause with HAVING clause in SQL −

In the following query, we are grouping the customers by their age and calculating the minimum salary for each group. Using the HAVING clause we are filtering the groups where the age is greater than 24 −

The result produced is as follows −

Home » Oracle Basics » Oracle GROUP BY

Oracle GROUP BY

Summary : in this tutorial, you will learn how to use the Oracle GROUP BY clause to group rows into groups.

Introduction to Oracle GROUP BY clause

The GROUP BY clause is used in a SELECT statement to group rows into a set of summary rows by values of columns or expressions. The GROUP BY clause returns one row per group.

The GROUP BY clause is often used with aggregate functions such as AVG() , COUNT() , MAX() , MIN() and SUM() . In this case, the aggregate function returns the summary information per group. For example, given groups of products in several categories, the AVG() function returns the average price of products in each category.

The following illustrates the syntax of the Oracle GROUP BY clause:

The GROUP BY clause appears after the FROM clause. In case WHERE clause is presented, the GROUP BY clause must be placed after the WHERE clause as shown in the following query:

The GROUP BY clause groups rows by values in the grouping columns such as c1 , c2 and c3 . The GROUP BY clause must contain only aggregates or grouping columns.

If you want to specify multiple levels of grouping that should be computed at once, you use the following ROLLUP syntax:

Please check out the ROLLUP tutorial for more information.

Oracle GROUP BY examples

We will use the following orders and order_items in the sample database for the demonstration:

A) Oracle GROUP BY basic example

The following statement uses the GROUP BY clause to find unique order statuses from the orders table:

This statement has the same effect as the following statement which uses the DISTINCT operator:

B) Oracle GROUP BY with an aggregate function example

The following statement returns the number of orders by customers:

In this example, we grouped the orders by customers and used the COUNT() function to return the number of orders per group.

To get more meaningful data, you can join the orders table with the customers table as follows:

Here is the result:

C) Oracle GROUP BY with an expression example

The following example groups the orders by year and returns the number of orders per year.

In this example, we used the EXTRACT() function to get the year information from the order’s dates.

Unlike the previous examples, we used an expression that returns the year in the GROUP BY clause.

The following picture illustrates the result:

D) Oracle GROUP BY with WHERE clause example

This example uses the GROUP BY clause with a WHERE clause to return the number of shipped orders for every customer:

Here is the output:

Note that the Oracle always evaluates the condition in the WHERE clause before the GROUP BY clause.

E) Oracle GROUP BY with ROLLUP example

The following statement computes the sales amount and groups them by customer_id , status , and ( customer_id , status ):

In this tutorial, you have learned how to use the Oracle GROUP BY clause to group rows into groups.

• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
• Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand
• OverflowAI GenAI features for Teams
• OverflowAPI Train & fine-tune LLMs
• Labs The future of collective knowledge sharing
• About the company Visit the blog

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Get early access and see previews of new features.

Using group by on multiple columns

I understand the point of GROUP BY x .

But how does GROUP BY x, y work, and what does it mean?

• multiple-columns

• 2 You won't find it described as this question poses it. The GROUP BY clause can take one or more fields. GROUP BY customer; GROUP BY lastname, firstname; GROUP BY year, store, sku etc. –  Bill Commented Mar 10, 2010 at 23:32

3 Answers 3

Group By X means put all those with the same value for X in the one group .

Group By X, Y means put all those with the same values for both X and Y in the one group .

To illustrate using an example, let's say we have the following table, to do with who is attending what subject at a university:

When you use a group by on the subject column only; say:

You will get something like:

...because there are 5 entries for ITB001, and 2 for MKB114

If we were to group by two columns:

we would get this:

This is because, when we group by two columns, it is saying "Group them so that all of those with the same Subject and Semester are in the same group, and then calculate all the aggregate functions (Count, Sum, Average, etc.) for each of those groups" . In this example, this is demonstrated by the fact that, when we count them, there are three people doing ITB001 in semester 1, and two doing it in semester 2. Both of the people doing MKB114 are in semester 1, so there is no row for semester 2 (no data fits into the group "MKB114, Semester 2")

Hopefully that makes sense.

• 42 @Smashery: So does this also mean that GROUP BY A,B is same as GROUP BY B,A ? –  tumchaaditya Commented Sep 26, 2014 at 18:08
• 71 Yes, it does. I can't say for certain whether they are as efficient as each other, but they will give the same result, yes. –  Smashery Commented Sep 29, 2014 at 0:10
• 5 May here should be added that there is a difference between GROUP BY a, b and GROUP BY a AND b since the second one only lists grouped items with exactly the same content and no "undergroups". In this case the output would be same like the first one. –  Dwza Commented Mar 4, 2015 at 15:06
• 7 I would like to add that the order in which you group by the columns does not matter. In the above example group by Semester, Subject would have given the same result –  user2441441 Commented Sep 29, 2015 at 21:07
• 6 well, group by a, b and group by b, a do NOT return the same result - the rows are displayed in a different order –  fanny Commented Oct 3, 2018 at 9:14

Here I am going to explain not only the GROUP clause use, but also the Aggregate functions use.

The GROUP BY clause is used in conjunction with the aggregate functions to group the result-set by one or more columns. e.g.:

Remember this order:

SELECT (is used to select data from a database) FROM (clause is used to list the tables) WHERE (clause is used to filter records) GROUP BY (clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns) HAVING (clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE) ORDER BY (keyword is used to sort the result-set)

You can use all of these if you are using aggregate functions, and this is the order that they must be set, otherwise you can get an error.

Aggregate Functions are:

MIN() returns the smallest value in a given column MAX() returns the maximum value in a given column. SUM() returns the sum of the numeric values in a given column AVG() returns the average value of a given column COUNT() returns the total number of values in a given column COUNT(*) returns the number of rows in a table

SQL script examples about using aggregate functions:

Let's say we need to find the sale orders whose total sale is greater than $950. We combine the HAVING clause and the GROUP BY clause to accomplish this:

Counting all orders and grouping them customerID and sorting the result ascendant. We combine the COUNT function and the GROUP BY , ORDER BY clauses and ASC :

Retrieve the category that has an average Unit Price greater than $10, using AVG function combine with GROUP BY and HAVING clauses:

Getting the less expensive product by each category, using the MIN function in a subquery:

The following will show you how to select the most recent date item " productDate ", using MAX function in a subquery:

The following statement groups rows with the same values in both categoryId and productId columns:

To answer a question: - what if use GROUP BY but no aggregate function? The answer is: We can also use the GROUP BY without applying the Aggregate function . Here is an example where we are grouping by categoryId :

• 4 but where do we put the 2 columns, how to aggregate based on 2/more columns is the question –  Chaitanya Bapat Commented Nov 17, 2017 at 22:13
• 7 This doesn't even remotely answer the question... The question here is how to acheive "chained grouping" of "subject" and "semester" at the same time, as explained in the given example... –  MahNas92 Commented May 20, 2019 at 10:22
• 1 The last example shows you how to put 2 columns using aggregate function. @ChaitanyaBapat –  S. Mayol Commented Jan 7, 2021 at 20:47
• This doesn't work in Oracle if you have more select column that you don't wan't to group. –  Marco Commented Aug 10, 2022 at 22:48
• what if use GROUP BY but no aggregate function? –  Alois Mahdal Commented Nov 30, 2023 at 14:07

In simple English from GROUP BY with two parameters what we are doing is looking for similar value pairs and get the count to a 3rd column.

Look at the following example for reference. Here I'm using International football results from 1872 to 2020

And now I'm going to group by similar country(column _c7 ) and tournament( _c5 ) value pairs by GROUP BY operation,

Explanation: The meaning of the first row is there were 11 Friendly tournaments held on Southern Rhodesia in total.

Note: Here it's mandatory to use a counter column in this case.

Your Answer

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Sign up or log in

Post as a guest.

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy .

Not the answer you're looking for? Browse other questions tagged sql group-by multiple-columns or ask your own question .

• The Overflow Blog
• Community Products Roadmap Update, July 2024
• Featured on Meta
• We spent a sprint addressing your requests — here’s how it went
• Upcoming initiatives on Stack Overflow and across the Stack Exchange network...
• The [lib] tag is being burninated
• What makes a homepage useful for logged-in users

Hot Network Questions

• Who is the distant man in the final frames?
• Decorate equations with arrows using TikZ
• When do people say "Toiletten" in the plural?
• Sort Number Array
• Can the US president legally kill at will?
• Why did the main wire to my apartment burn before the breaker tripped?
• PowerShell manipulation of `for` results
• The meaning of "奪耳" in 《說文解字》
• Air magic only used to decrease humidity and improve living conditions?
• Where is the pentagon in the Fibonacci sequence?
• Simulate slow disks in KVM to see effect of LVM cache in test setup
• Is "necesse est tibi esse placidus" valid classical Latin?
• 2024 UK election: Which demographic factors explain the geography?
• Is it possible to easily change the inclination when using momentum exchange tethers?
• When can まで mean "only"?
• Were there any stone vessels (including mortars) made in Paleolithic?
• Do you always experience the gravitational influence of other mass as you see them in your frame?
• Formalizing the construction of a Cantor scheme
• Improve spacing around equality = and other math relation symbols
• Visa requirements for British passport holders going to Ghana via Brussels
• How do I drill a 60cm hole in a tree stump, 4.4 cm wide?
• ForeignFunctionLoad / RawMemoryAllocate and c-struct that includes an array
• Is a desert planet with a small habitable area possible?
• When selling a machine with proprietary software that links against an LGPLv3 library, do I need to give the customer root access?

How to write a handover email to a client

Are you leaving a role, and thinking about how to manage transferring your client work to a colleague?

Here’s how to do a smooth handover process, and there’s an email template below you can use to tell clients that you are moving on.

Why are you writing the email?

First, let’s get clear on why you want to write the handover mail. Normally, when I’ve had to let clients (or vendors) know that I’m going to be out for a while it’s because either:

• I am leaving the job and want to reassure them that there will be a smooth handover
• I’m letting them know that I’ll be out off the office for some time, and want to reassure them.

Either way, the purpose of writing a handover email to a customer is to let them know that there won’t be disruption in the service your company provides to them.

If you’re writing handover notes for a colleague, read my complete guide to doing a work handover instead. That one also has email templates!

Let them know before the email

Ideally, your handover message will not be news to the client.

A professional handover is never a surprise.

Call the customer first and let them know, or if you aren’t senior enough to do that, have your manager call them and break the news.

The contents of the handover email is more about reassurance, continuity and wrapping up any tasks you specifically had open for them.

Explain the handover process

An important part of your client handover is letting them know what’s happening. No one likes to be in the dark, and if you’re paying for a service, even more so.

Talk to your client about the process for completing your handover. Normally this will be making sure you do a good job of briefing a colleague who will be taking the lead when you move on. Ideally, there will be some time for you to work alongside them, training them before you go.

If you can, introduce them to the client via your normal account management meetings so they have both of you in the room (or on screen) for a couple of weeks.

Be available for questions

Make sure your handover starts early enough for you to be around to smooth things over.

Clients may have questions, or they may want to highlight some particular details that they want you to pass on. If you can, be around to answer all of those gracefully.

If you don’t know the answers, make sure to point them to someone who does.

Your replacement might as well start building a relationship with the person who will be managing the work going forward, so if they have already started, get them to reply to questions and copy you in.

What happens when you don’t have time for a proper handover?

Let’s assume your dream job has come up and you’re leaving tomorrow. Or some personal situation means you have to leave with hardly any notice.

You can still send a handover email. You might want to add more detail (not about your personal circumstances) and you’ll certainly want to do a detailed handover to the colleague, even if you have to do that in writing.

Write a handover email to your manager , and point out where the core information is that they will need to ensure continuity of service.

Template: What to put in your handover email

Here’s a sample handover email template that you can customize to send to your clients.

Following our conversation, I wanted to formally introduce you to [COLLEAGUE’S NAME] who will be picking up the XXX account/project from me. [NAME] is a fantastic project manager/account manager etc with X years’ experience leading similar initiatives. HIS/HER contact details are:

[INSERT DETAILS]

I’ll be completing a full internal handover so please be assured that HE/SHE will be able to pick up seamlessly when I leave on [DATE].

The next steps that we are working on together and that I’m handing over are:

• NEXT STEP 1
• NEXT STEP 2.

Thank you so much for your engagement/support on this project to date. It has been a pleasure to work with you and I’m sure with [NAME] supporting the work going forward, it will be a huge success.

If you have any questions about the transition process, please reach out to [MANAGER’S NAME].

Close with your normal closure/signature.

Copy in your colleague and your manager/account manager as necessary.

If the project has come to a natural close, read more about how to handover a project on closure as you’ll probably want to include different details.

Should you provide your contact information?

I don’t think it’s professional to include your personal contact information. You might be accused of trying to poach clients if you provide details of where you are going next or your personal email address.

The relationship is between the organization and the client, not you personally. It’s not necessary or appropriate to encourage them to stay in contact at this point.

After the handover email is sent

Now you’re all ready to leave and your client has the contact details for your replacement.

I would now think about what you have learned working with this customer. Can you copy all your achievements, key tasks and responsibilities to your LinkedIn profile?

If you got on particularly well with the client and it wouldn’t be weird, why not connect with them on LinkedIn and ask them for a recommendation? It’s an easy way to build your network and it makes more sense to do it soon, rather than leaving it a year or so and wondering if they remember you.

Leaving a role and having to say goodbye to clients is a normal part of building your career, so don’t feel bad about sharing the news that you are moving on. Use the email outline here as a guide for how you are going to communicate with your clients, and take the next step forward with confidence.

Project manager, author, mentor

Elizabeth Harrin is a Fellow of the Association for Project Management in the UK. She holds degrees from the University of York and Roehampton University, and several project management certifications including APM PMQ. She first took her PRINCE2 Practitioner exam in 2004 and has worked extensively in project delivery for over 20 years. Elizabeth is also the founder of the Project Management Rebels community, a mentoring group for professionals. She's written several books for project managers including Managing Multiple Projects .

• Resource Collection
• State Resources

Community for Adult Educators

• Public Groups
• Micro Groups
• Recent Activity
• Reading and Writing
• Discussions

Save the Date for These Upcoming Reading and Writing Community Events!

Please save the date for these upcoming Reading and Writing Group LINCS events! 

Differentiating Instruction in the ESL Classroom - July 10 @ 3 pm ET Managing a multi-level classroom can be challenging! Join this live session to gain practical tips to work more effectively in multi-level ESOL classrooms. Our guest presenter, Dr. Katie Welch, a Linguist, and professional development instructor to Teaching English to Speakers of Other Languages (TESOL) will outline teaching differentiations designed to address the needs of learners at various levels. Register here . 

Understanding the Science of Reading - August 20 @ 3:30 pm ET

Confused about the Science of Reading (SOR) and how to apply its principles to instruction in your adult education program? Join LINCS moderators Dr. Carmine Stewart, Steve Schmidt, and Dr. Kathy Tracey as they unpack the SOR and provide information on LINCS resources that will help elevate your reading instruction. 

Facilitating Conversations About Difficult Topics - September 26 @ 3:30 PM ET

Wondering about best practices for having difficult classroom conversations on topics like race, politics, and other controversial issues? The live event, Facilitating Conversations about Difficult Topics, that will be held on September 26 at 3:30 PM ET will provide answers. (Note: Event is pending final OCTAE approval)

An Open Discussion Coffee Break - November 7 @ 3:30 PM ET

Adult educators say they seldom have the chance to talk with their colleagues in a relaxed setting. You will have that chance in a LINCS open discussion coffee break on November 7 at 3:30 PM ET. Come prepared to share and learn! (Note: Event is pending final OCTAE approval)

From Overwhelm to Optimal Learning: Harnessing Schemas and Decreasing Cognitive Load - November 11 @ 3 PM ET

Join us for an insightful coffee break that delves into two foundational theories from cognitive science: Cognitive Load Theory and Schema Theory. In this session, we will explore how these theories relate to teaching and learning, providing you with practical instructional strategies to optimize your educational practices. (Note: Event is pending final OCTAE approval)

• Log in or register to post comments

By Catherine E. Shoichet, Alicia Johnson, James Grant and Leah Abucayan, CNN

Published June 30, 2024

How do you pick which emoji to use? Do you worry when you see a period at the end of a text? Are you puzzled by messages from your parents, kids or coworkers?

One reason why: People in different generations often text differently. It’s not an exact science, but interesting theories and trends are emerging as researchers delve into the texts and messages we send.

Take our quiz to see what your texting style says about you, and how well you understand other generations. We’ll give you a few results along the way and a final score at the end.

How would getting a message like this from your boss make you feel?

Your coworker just sent you a hilarious message. How do you respond?

How do you choose which emojis to include in a text?

Gen Z: This generation tends to use and interpret emojis more figuratively or ironically than older generations. A 2022 Adobe study found members of Gen Z were significantly more likely to agree they used emoji differently than their intended meanings.

Millennials: This generation is more likely to use emoji to convey emotions , but sometimes attaches more symbolic meanings to them.

Gen X: This generation is generally more practical in their use of technology, and less likely to use emoji .

Baby Boomers: University of Ottawa researchers found this generation was less likely to use emojis and struggled to interpret some emojis, such as 😳. Others found some Baby Boomers used emojis frequently, but were unaware of potential double meanings behind them.

Keep in mind: Researchers say emojis can add important meaning to text messages, which lack the gestures and facial cues you see when talking with someone in person. But the symbols can also be misunderstood. And age isn’t the only factor that can contribute to confusion; culture , gender and evolving emoji meanings also play a role.

There’s a traffic jam and your Uber is running late. Which text would you send?

When writing a text message, how do you use periods.

In what circumstances do you write something in all caps?

Gen Z: A recent analysis by undergraduates at UCLA found “a stronger preference in Gen Z for using messages that convey a stronger or louder tone” by using all-caps. Linguist Deborah Tannen of Georgetown University describes how her younger students use repeated letters in communication, like beginning a message “hiiiiiiiii,” to convey enthusiasm.

Millennials: Like their younger counterparts in Gen Z, this generation tends to avoid ending texts with periods. They’re also more likely to play with punctuation to convey what linguist Gretchen McCulloch calls “typographical tone of voice.”

Gen X: Texters in this oft-overlooked generation are more likely to apply traditional punctuation rules to their messages, as Miami University Management and Leadership Professor Megan Gerhardt noted in a recent LinkedIn post describing her surprise when her Gen Z students pointed out their feelings about seeing periods at the end of texts.

Baby Boomers: This group is more likely to apply the rules of formal letter-writing to their texts. Niki Tonks, a marketing expert who teaches at Weber State University in Utah, says her surveys found Baby Boomers prefer full sentences in text messages and are less likely to understand shifting nuances of punctuation.

Keep in mind: Many of us break rules we’ve learned about grammar and punctuation when we send text messages. Linguist John McWhorter argues that’s because texting is more akin to “written speech” than a formal piece of writing.

How would you feel if a romantic partner responded to a text from you by saying “K” or 👍?

Look at your phone. How many text messages did you send and receive yesterday?

When you send a text message, how quickly do you expect to get a response?

Gen Z: Why send one text when 10 will do? Researchers have found texters in this generation send and receive a greater volume of messages than those in older generations. They’re also likely to expect quicker responses and worry more about delays.

Millennials: Researchers have found this generation generally sends and receive fewer texts than their younger counterparts, but their phones still spend plenty of time buzzing. Millennials send and receive a high volume of messages, and generally expect quick responses .

Gen X: This generation sends a moderate volume of text messages. And unlike their younger counterparts, they worry less about response times. Maybe they’re busy watching the new Brat P ack documentary instead?

Baby Boomers: This generation is getting a lot more conversant in texting, but they’re not quite fluent. Baby Boomers generally send and receive a lower volume of text messages than those in younger generations, and they’re not as concerned about response times.

Keep in mind: Unwritten rules shape the way different generations text each other, and how they feel about the interactions, according to Niki Tonks, a marketing expert who teaches at Weber State University in Utah. But our texting style isn’t set in stone based on our birth year. In fact, we may code-switch when we text, just like many of us do when we speak, adopting tones and approaches that are similar to the person we’re texting with, and adapting our expectations accordingly.

Read this conversation between a Gen Z son and his Gen X mom:

Is the mom mad at her son?

The mom is just trying to be efficient and direct. This text exchange between marketing expert Niki Tonks and her son inspired her research . Her son later explained that he’d been worried she was mad because of her short replies.

What are these generations’ top 5 favorite emojis?

These rankings of favorite emoji came from Adobe’s 2022 U.S. Emoji Trend Report. That survey also found that 74% of Gen Z emoji users say they use emoji differently than their intended meanings, compared to 65% of Millennials, 48% of Gen X respondents and 24% of Baby Boomers.

In 2022, tech company Giphy pointed to the growing use of GIFs by this generation as it asked British regulators not to block a deal for its acquisition by Meta.

“Marketplace commentary and user sentiment towards GIFs on social media shows that they have fallen out of fashion as a content form, with younger users in particular describing GIFs as ‘for boomers’ and ‘cringe,’” Giphy said in an August 2022 filing .

How well do you understand Gen Z?

A recent analysis from UK tech retailer Currys listed these translations. Here’s a quick glossary of the Gen Z terms:

DIFTP This acronym is short for “do it for the plot.” It gained steam on TikTok in 2022.

Slaps This term has its roots in hip-hop and became more mainstream in the late 2010s.

💀 This emoji has come to signify “I’m dead” and is used to convey laughter.

Tea Like many words now known as Gen Z slang, this phrase originated in Black drag culture .

True or false: Millennials were the first generation to popularize smiley faces in text communication as emojis became increasingly popular.

We actually have Baby Boomer Scott Fahlman, an emeritus professor at Carnegie Mellon University, to thank for inventing the emoticon in 1982.

What year were you born?

Here are your full results:

Edito r s’ Notes

How we did this: We spoke with researchers, read studies and drew upon our own personal experiences to shape the questions in this quiz. Some questions are based on research findings or echo survey questions used in studies. Others were inspired by expert interviews or anecdotal observations.

What's next: We hope this quiz sparks conversation and encourages more research into these important issues. We plan to analyze responses we receive to see how they compare with generational expectations.

More From Forbes

Elevate team performance rapidly: group coaching magic.

• Share to Facebook
• Share to Twitter
• Share to Linkedin

The Power of Group Coaching

I was meeting with a CEO the other day who is looking to improve the skills of his management team. Ideally, he'd love to hire a private coach for each team member but doesn't have the funds to do so, so I suggested an alternative—group coaching.

In today's fast-paced business world, business leaders face a constant challenge: developing their leadership team without breaking the bank.

Enter group coaching, the savvy solution that delivers maximum impact with minimal investment.

Many of you have experienced first-hand how coaching can transform individuals and elevate performance. But let's face it: hiring a coach for every management team member is a luxury few companies can afford.

That's where group coaching comes in, offering a potent blend of peer learning, accountability, and skill development that can supercharge your leadership team's effectiveness.

Here's how you can gain maximum benefit with minimal investment with group coaching:

Fast Results with Minimal Investment

Group coaching allows you to develop multiple leaders simultaneously, stretching your coaching budget further than you thought possible. Instead of paying for individual sessions, you're investing in a collective growth experience that benefits the entire team and the overall organization.

Best High-Yield Savings Accounts Of 2024

Best 5% interest savings accounts of 2024.

One brain is good, but several are better. Group coaching brings together leaders from various departments, creating a melting pot of perspectives and experiences. This diversity sparks innovative thinking and problem-solving that individual coaching simply can't match. It also enables team members to have a shared experience, which helps to improve interdepartmental relationships and employee retention.

Real-Time Learning

Group coaching allows your team to address real challenges in real-time. This immediate application of learning ensures that the insights gained translate directly into improved performance.

Breaking Down Silos

Group coaching breaks down barriers between departments, fostering collaboration and understanding across different business areas. Suddenly, your head of marketing has a much deeper understanding of how to best position your company for growth, while your VP of sales now understands why operations is unable to deliver on the sales team's promises.

Accountability on Steroids

When leaders commit to goals in front of their peers, they follow through, knowing their colleagues are watching. This peer pressure drives results far more effectively than any external motivator.

Scaling Leadership

Group coaching allows you to deploy leadership development across your organization quickly. Instead of relying on the trickle-down impact of individual coaching, you're creating a ripple of growth that flows to every corner of the company.

Group coaching isn't just a cost-effective solution; it's a strategic move that can transform your organization from the inside out. Now's the time to unlock your team's potential and watch them soar!

• Editorial Standards
• Reprints & Permissions

Join The Conversation

One Community. Many Voices. Create a free account to share your thoughts. 

Forbes Community Guidelines

Our community is about connecting people through open and thoughtful conversations. We want our readers to share their views and exchange ideas and facts in a safe space.

In order to do so, please follow the posting rules in our site's  Terms of Service.   We've summarized some of those key rules below. Simply put, keep it civil.

Your post will be rejected if we notice that it seems to contain:

• False or intentionally out-of-context or misleading information
• Insults, profanity, incoherent, obscene or inflammatory language or threats of any kind
• Attacks on the identity of other commenters or the article's author
• Content that otherwise violates our site's  terms.

User accounts will be blocked if we notice or believe that users are engaged in:

• Continuous attempts to re-post comments that have been previously moderated/rejected
• Racist, sexist, homophobic or other discriminatory comments
• Attempts or tactics that put the site security at risk
• Actions that otherwise violate our site's  terms.

So, how can you be a power user?

• Stay on topic and share your insights
• Feel free to be clear and thoughtful to get your point across
• ‘Like’ or ‘Dislike’ to show your point of view.
• Protect your community.
• Use the report tool to alert us when someone breaks the rules.

Thanks for reading our community guidelines. Please read the full list of posting rules found in our site's  Terms of Service.

We finally know why some people got COVID while others didn’t

Principal Research Fellow/Honorary consultant Respiratory Medicine, UCL

Postdoc Research Fellow, Molecular and Cellular Biology, UCL

Disclosure statement

Marko Nikolic receives funding from the Medical Research Council, Action Medical Research, Rosetrees Trust and the Chan Zuckerberg Initiative.

Kaylee Worlock receives funding from the Medical Research Council.

University College London provides funding as a founding partner of The Conversation UK.

View all partners

Throughout the pandemic, one of the key questions on everyone’s mind was why some people avoided getting COVID, while others caught the virus multiple times.

Through a collaboration between University College London, the Wellcome Sanger Institute and Imperial College London in the UK, we set out to answer this question using the world’s first controlled “challenge trial” for COVID – where volunteers were deliberately exposed to SARS-CoV-2, the virus that causes COVID, so that it could be studied in great detail.

Unvaccinated healthy volunteers with no prior history of COVID were exposed – via a nasal spray – to an extremely low dose of the original strain of SARS-CoV-2. The volunteers were then closely monitored in a quarantine unit, with regular tests and samples taken to study their response to the virus in a highly controlled and safe environment.

For our recent study , published in Nature, we collected samples from tissue located midway between the nose and the throat as well as blood samples from 16 volunteers. These samples were taken before the participants were exposed to the virus, to give us a baseline measurement, and afterwards at regular intervals.

The samples were then processed and analysed using single-cell sequencing technology, which allowed us to extract and sequence the genetic material of individual cells. Using this cutting-edge technology, we could track the evolution of the disease in unprecedented detail, from pre-infection to recovery.

To our surprise, we found that, despite all the volunteers being carefully exposed to the exact same dose of the virus in the same manner, not everyone ended up testing positive for COVID.

In fact, we were able to divide the volunteers into three distinct infection groups (see illustration). Six out of the 16 volunteers developed typical mild COVID, testing positive for several days with cold-like symptoms. We referred to this group as the “sustained infection group”.

Out of the ten volunteers who did not develop a sustained infection, suggesting that they were able to fight off the virus early on, three went on to develop an “intermediate” infection with intermittent single positive viral tests and limited symptoms. We called them the “transient infection group”.

The final seven volunteers remained negative on testing and did not develop any symptoms. This was the “abortive infection group”. This is the first confirmation of abortive infections, which were previously unproven . Despite differences in infection outcomes, participants in all groups shared some specific novel immune responses, including in those whose immune systems prevented the infection.

When we compared the timings of the cellular response between the three infection groups, we saw distinct patterns. For example, in the transiently infected volunteers where the virus was only briefly detected, we saw a strong and immediate accumulation of immune cells in the nose one day after infection.

This contrasted with the sustained infection group, where a more delayed response was seen, starting five days after infection and potentially enabling the virus to take hold in these volunteers.

In these people, we were able to identify cells stimulated by a key antiviral defence response in both the nose and the blood. This response, called the “interferon” response, is one of the ways our bodies signal to our immune system to help fight off viruses and other infections. We were surprised to find that this response was detected in the blood before it was detected in the nose, suggesting that the immune response spreads from the nose very quickly.

Protective gene

Lastly, we identified a specific gene called HLA-DQA2, which was expressed (activated to produce a protein) at a much higher level in the volunteers who did not go on to develop a sustained infection and could hence be used as a marker of protection. Therefore, we might be able to use this information and identify those who are probably going to be protected from severe COVID.

These findings help us fill in some gaps in our knowledge, painting a much more detailed picture regarding how our bodies react to a new virus, particularly in the first couple of days of an infection, which is crucial.

We can use this information to compare our data to other data we are currently generating, specifically where we are “challenging” volunteers to other viruses and more recent strains of COVID. In contrast to our current study, these will mostly include volunteers who have been vaccinated or naturally infected – that is, people who already have immunity.

Our study has significant implications for future treatments and vaccine development. By comparing our data to volunteers who have never been exposed to the virus with those who already have immunity, we may be able to identify new ways of inducing protection, while also helping the development of more effective vaccines for future pandemics. In essence, our research is a step towards better preparedness for the next pandemic.

• Coronavirus
• Challenge trial

Sydney Horizon Educators – Faculty of Engineering (Targeted)

Dean, Faculty of Education

Lecturer in Indigenous Health (Identified)

Social Media Producer

PhD Scholarship

• Newsletters
• Account Activating this button will toggle the display of additional content Account Sign out

Can the President Send SEAL Team Six to Assassinate His Rival? After Monday, Yes.

On Monday, the Supreme Court handed down a much-awaited and long-delayed decision in Trump v. United States , testing the former president’s claim to absolute immunity from criminal prosecution for the events surrounding the attack on the Capitol on Jan. 6, 2021. The 6–3 decision, penned by Chief Justice John Roberts and joined by all of the court’s conservatives, broadly expanded the zone of presidential immunity in ways that fundamentally reshape the balance of powers in American government, creating extraordinary new protections for a president who seeks to break the law. As Justice Sonia Sotomayor warned in her dissent, if the president “orders the Navy’s Seal Team 6 to assassinate a political rival,” he is now “insulated from criminal prosecution.” The “relationship between the president and the people he serves,” Sotomayor continued, “has shifted irrevocably in every use of official power. The president is now a king above the law.”

On a Slate Plus bonus episode of Amicus discussing the scope of the decision, Dahlia Lithwick and Mark Joseph Stern were joined by professor Corey Brettschneider, who teaches constitutional law and political theory at Brown University and is the author of the new book The Presidents and the People: Five Leaders Who Threatened Democracy and the Citizens Who Fought to Defend It . Their conversation has been edited and condensed for clarity.

Dahlia Lithwick: This opinion is a game changer, but the chief justice characteristically tried to make it sound modest. The majority opinion as penned by the chief justice differentiates between criminal acts that are official and unofficial acts. Help us understand if the court has a marker of the difference between the two, what that line is, and why it matters.

Mark Joseph Stern: I don’t see how the trial court can apply this decision with any modicum of certainty or consistency. Because what Roberts has delivered is a Ziploc bag full of muddy water. I think that’s the whole point. In practice, this is just going to mean that anytime the president says “I was doing that pursuant to my constitutional authority,” the courts are going to grant him immunity. As Justice Sonia Sotomayor argues persuasively in her dissent, this new rule is going to prevent prosecutors from charging a president with the crime of manipulating the tools of his office to engage in corruption. Because as long as the president defends himself in line with Roberts’ opinion, that’s not even a crime in the first place.

Lithwick: Corey, the court is drawing all these lines, like “core functions” vs. “outer perimeter,” that make it sound like they’re solving a math problem. Can you help us understand what a sea change this actually is? Because this is not just a Donald Trump story. This is a separation-of-powers story and fundamentally a structural change to democracy as we understand it.

Corey Brettschneider: You know, the court claims that it has this method of originalism that requires us to look at the text of the Constitution as it was originally understood. So you would expect, in a monumental decision like this—granting immunity to a former president’s “official acts”—that the Constitution would say something about presidential immunity. But it doesn’t. There was a disagreement at the founding about this: John Adams, for instance, defended the idea that presidents are immune from prosecution, which suits his disposition toward an aggrandized idea of presidential power. On the other side, though, there was James Wilson; at the Pennsylvania ratifying convention, he said the thing that we thought defined our Constitution until Monday—that no person, not even a president, is above the law, and the president can be prosecuted.

So it’s not like there was some universal understanding of presidential immunity at the founding. There’s no textual evidence in the Constitution. The Framers just disagreed when they talked about this issue. So the idea that this is based on originalism is false. And the main precedent they do rely on, Nixon v. Fitzgerald , gets extended way beyond what it actually said. I’d argue Roberts is saying something contrary to what that case actually said. And we have stronger cases on the other side, like U.S. v. Nixon , which held that a president facing a criminal subpoena is not above the law. They may claim to respect precedent, but they’re not doing it. They may claim to respect text, but they aren’t. And they may claim to respect history, as if the Framers sort of agreed about this, but that’s not true either.

Stern: I love Justice Sotomayor’s two citations to the Dobbs decision in her dissent. She makes the scathing point that “it seems history only matters to this court when it is convenient.” And that’s the whole ballgame. The court will cherry-pick whatever fits its narrative, then ignore everything else. In this instance, it’s especially egregious because the Framers knew how to put immunity in the Constitution—they gave it to members of Congress, after all, but not the president. And now, centuries later, six justices decide they know better than the Framers.

When it came to reproductive autonomy, the conservative justices said, We have to leave this to the people and their representatives—it’s just a matter of policy . But when it comes to making the president a king and undoing the compromises of the Constitutional Convention that established the executive as it existed until Monday morning? All that can be wiped away with a bit of gauzy rhetoric about how important it is to have an “energetic” executive.

Lithwick: Mark and I have spent the past couple months talking about a monarchic Supreme Court that is uncheckable on any front we can discern, and the sense that the uncheckable judiciary is not fully appreciated by the nation. But this move, from a monarchic judicial branch to a monarchic presidency, happens at the speed of light. I’m just thinking about what it means to have a two-headed monarchy in the place of checks and balances. And I’m wondering: After this decision, can the president actually call in SEAL Team Six in order to have a political opponent assassinated and avoid prosecution by saying he was exercising his constitutional authority? Can the president just invoke the Insurrection Act in order to break up a lawful protest? What’s going to constrain this theory of what feels, to me, like unlimited power and zero accountability as long as the president deems it an “official act”?

Brettschneider: Certainly not this court. And that’s what’s so important to get across—this is really about the fundamentals of democracy. We’re talking about the president committing crimes with impunity that threaten the stability of democracy itself. And the presidency has become so powerful that if Trump wins in November, he could use his office to literally destroy democracy.

How do you stop that? You would hope the courts would say that a president who committed a crime can be prosecuted after they leave office. But this Supreme Court has abdicated that responsibility. It has gotten rid of so many checks on the presidency that we took for granted. But we can’t let them have the last word on this. Citizens have got to take the Constitution back. We’ve got to demand legislation that limits the office, and the appointment of justices who see things with common sense. But where we’ve historically been able to push back is through elections. We’ve got to figure out how to make this election a referendum on democracy. Because Trump has now essentially been protected in all things, even as outrageous as a political assassination of his opponent. And no president should be able to do that.

Stern: Can I draw out a point that was implicit in what you just said, Corey? Through Roberts, the majority draws out this distinction between official and unofficial acts as though it’s the most natural thing in the world—like, Of course we wouldn’t want the president to be punished for committing crimes in the course of exercising the duties of his office .

But as Justice Sonia Sotomayor explained in her dissent, the lesson from Nixon is that when presidents use their power to commit official acts in pursuit of a criminal scheme, that is when we should be most concerned about their conduct and most eager for them to face accountability. Because that is when their criminal conduct poses the most heightened threat to democracy. If the president can take this immense power we entrust him with and manipulate it for corrupt ends—knowing he will never face accountability—then we have fundamentally changed the nature of what the presidency is. He is no longer a person who is elected by the people to represent them for four years. He is an emperor who walks into office, surveys his new powers, and gets to decide how to use them for his own corrupt advantage—to enrich himself, to entrench his authority, to prevent the peaceful transition to power, to even execute his rivals. I just think that is exactly the opposite of how the executive power had been understood until Monday morning. And I find myself baffled that six justices would say that presidential accountability is now constitutionally prohibited rather than constitutionally required.

MySQL Tutorial

Mysql database, mysql references, mysql examples, mysql group by statement, the mysql group by statement.

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions ( COUNT() , MAX() , MIN() , SUM() , AVG() ) to group the result-set by one or more columns.

GROUP BY Syntax

Demo database.

Below is a selection from the "Customers" table in the Northwind sample database:

Advertisement

MySQL GROUP BY Examples

The following SQL statement lists the number of customers in each country:

The following SQL statement lists the number of customers in each country, sorted high to low:

Below is a selection from the "Orders" table in the Northwind sample database:

And a selection from the "Shippers" table:

GROUP BY With JOIN Example

The following SQL statement lists the number of orders sent by each shipper:

Test Yourself With Exercises

List the number of customers in each country.

Start the Exercise

COLOR PICKER

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail: [email protected]

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail: [email protected]

Top Tutorials

Top references, top examples, get certified.

Copa America

Copa America 2024 quarterfinals bracket: Full knockout stage schedule

The quarterfinals of the 2024 Copa America are set.

Favorites Argentina , Uruguay , Colombia and Brazil are all still in contention as the tournament reaches its business end in the United States.

Unfortunately for the USMNT they won’t be part of it after they were eliminated in the group stage. Defeat to Panama in their second game left their progression to the next phase in doubt before a loss against Uruguay sealed their fate.

Advertisement

Lionel Messi ’s Argentina are defending champions, having lifted the title for a record-equalling 15th time last time out in Brazil in 2021.

Uruguay share that record with the reigning world champions and under Marcelo Bielsa are beginning a new era while Brazil, with stars like Vinicius Junior , Rodrygo and Alisson, are always likely contenders.

Here’s how the last eight shakes out:

Who qualified for Copa America knockout stages?

• Group A: Argentina
• Group A: Canada
• Group B: Venezuela
• Group B: Ecuador
• Group C: Uruguay
• Group C: Panama
• Group D: Colombia
• Group D: Brazil

Copa America 2024: A beginner's guide

What is the schedule?

Quarterfinals.

• Argentina 1-1 Ecuador (4-2 penalties)
• Venezuela vs Canada (9pm ET, AT&T Stadium, Arlington)
• Colombia vs Panama (6pm ET, State Farm Stadium, Glendale)
• Uruguay vs Brazil (9pm ET, Allegiant Stadium, Las Vegas)
• Argentina vs Venezuela or Canada (8pm ET, MetLife Stadium, East Rutherford)
• Uruguay or Brazil vs Colombia or Panama (8pm ET, Bank of America Stadium, Charlotte)

3rd place play-off

• Match 31 L29 vs L30 (8pm ET, Bank of America Stadium, Charlotte)
• Match 32 W29 vs W30 (8pm ET, Hard Rock Stadium, Miami)

(Top photo: Juan Mabromata/AFP via Getty Images)

Get all-access to exclusive stories.

Subscribe to The Athletic for in-depth coverage of your favorite players, teams, leagues and clubs. Try a week on us.

Ben Burrows is News Editor for The Athletic based in London. Prior to joining in 2023 he was Sports Editor at The Independent. Follow Ben on Twitter @ benburrows_

We've detected unusual activity from your computer network

To continue, please click the box below to let us know you're not a robot.

Why did this happen?

Please make sure your browser supports JavaScript and cookies and that you are not blocking them from loading. For more information you can review our Terms of Service and Cookie Policy .

For inquiries related to this message please contact our support team and provide the reference ID below.

How to Use COUNT() with GROUP BY: 5 Practical Examples

• aggregate functions
• numerical functions

Table of Contents

When to Use GROUP BY

When to use the count() function, example #1: group by a single column, example #2:  group by multiple columns, example #3: using where with count() and group by, example #4: using order by with count() and group by, count(*) - counting rows, counting non-null values in a column, counting distinct non-null values in a column, want to know more about count() and group by.

Using the COUNT() function with GROUP BY is one of the most common SQL constructs in aggregate queries. Read this article to find out how to use COUNT() with GROUP BY correctly using 5 examples.

In this article, we will explain the importance of using COUNT with GROUP BY . We’ll talk about why it is essential in SQL and how it enables data analysis and summarization based on specific criteria. This combination empowers users to extract meaningful insights, calculate counts, and generate statistical summaries from large datasets.

If you’re looking for an in-depth review of basic SQL concepts like COUNT() and GROUP BY , I recommend our interactive SQL Basics course. It contains 129 exercises, which will help you review all key SQL concepts.

How to Use COUNT() and GROUP BY

If you’re here just for the quick answer, here’s the TLDR:

The correct way of using COUNT() with GROUP BY is shown in the query below:

Let's break down the result to understand how this query works. The rows with the same value in the Store column are grouped together. Imagine an intermediate table where these rows are grouped and marked with different colors, like the image below. This would be our intermediary table containing only the Store column, since that is the column that is part of our SELECT statement.

The database then logically counts the number of rows in each group using the COUNT (*) function. This count represents the number of orders for each store.

Once the rows are counted, there is no need to have duplicate rows with the same Store value. So, GROUP BY will reduce the number of rows to only unique values. In other words, it will get rid of the individual rows and give us a summary of each group. We end up with the following result:

GROUP BY is an SQL clause that groups rows based on one or more column values . It is often used in combination with aggregate functions like COUNT() , SUM() , AVG() , MAX() , and MIN() to perform calculations on grouped data.

The GROUP BY clause is useful when you want to:

• Make calculations and aggregations on subsets of data.
• Generate summary statistics and metrics for different groups or categories.
• Identify patterns and trends within specific groups.
• Generate reports and analyze data based on different dimensions or attributes.
• Apply filters and conditions on grouped data, using the HAVING clause

In summary, GROUP BY is used to organize and summarize data based on specific columns, functions or expressions, which will allow you to gain insights and perform calculations on distinct groups within a dataset.

COUNT() is one of SQL’s most common aggregate functions. It returns the number of rows that match a specified condition or are included in a result set. It is often used to retrieve the total number of records in a table or to calculate the number of occurrences of a particular value within a column.

5 Examples of Using COUNT() with GROUP BY

Now that we’ve gone through the basic scenarios where COUNT() and GROUP BY are used, let’s go over some of the more complicated examples. We’ll start off with simpler examples and work our way up to more complex scenarios.

The simplest scenario you could encounter is when you need to GROUP BY a single column. In the following example, we need to find out how our company’s employee count is distributed across different job titles.

Before seeing the SQL solution to this scenario, let’s go over the sample data. This is the employees table:

By using the COUNT function with GROUP BY on the JobTitle column, we can get a breakdown of the number of employees in each specific role. You can see the query and the output (based on the sample data) below:

This example works the same way as our initial query. GROUP BY puts the rows for employees with the same job title into one group. Then the COUNT() function counts the rows in each group. GROUP BY then collapses the rows in each group, keeping only the value of the column JobTitle and the count.

Of course, you can group rows by more than one column.

In this example, we will look at a sample orders table containing basic order information:

We need to write a query that will show the number of orders placed by each customer and the ProductCategory of that order. This means that we will have to return the CustomerID and the category that the order falls within.

The query will look like this:

And the result of running this query can be seen below:

Our query groups rows by two columns: CustomerID and ProductCategory . This means that GROUP BY groups rows with the same values of CustomerID and ProductCategory into one group. (Rows for CustomerID 101 and the category Electronics are in one group, but rows for CustomerID 101 and the category Home Goods are in a different group.) Then the rows in each group are counted by COUNT() .

Our previous example analyzed a scenario where we wanted to create an aggregate of all of the information in our orders table. But sometimes, we might want to slice into this information and only see orders for selected categories.

Using the same sample data that we had before, we will now write a query that shows the same information for orders that fall within the “Accessories” or “Clothing” ProductCategory .

To do this, we can use the query from Example 2 and just add a WHERE clause. This clause will filter for records where ProductCategory is equal to “Accessories” or “Clothing”.

If it’s not entirely intuitive how the database has generated the results using the query above, here’s a step-by-step explanation of what happened behind the scenes:

• First, the database scans the orders table and reads all the rows.
• It then applies the filtering condition in WHERE ProductCategory IN (‘Accessories’, ‘Clothing’ ) to filter the rows. After this step, only the rows where the product category is “Accessories” or “Clothing” are considered for further processing.
• The filtered rows are then grouped based on the values in the CustomerID and ProductCategory columns, which are specified in the GROUP BY clause.
• For each of the unique combinations of CustomerID and ProductCategory , the COUNT(*) function is applied. This will count the number of rows within each group.
• The final result set includes the CustomerID , ProductCategory and the count of orders (shown in the NumberOfOrders column) for each group.

In summary, the database will filter the rows that respect the specified filtering condition. It will then group them according to the specified columns in the GROUP BY clause, and then calculate the count of orders within each of those groups. The final output will include the CustomerID , ProductCategory and the corresponding count of orders for each unique combination of CustomerID and ProductCategory .

Looking at the result from example number 4, we can see that some of the rows in the output are mixed. This is because the columns in the GROUP BY list will generally require – but not guarantee – sorting the output based on the list of columns in the GROUP BY section.

But if, for example, we need to organize the results on different conditions ( e.g. a timeline or by date) we will need to use an ORDER BY clause :

We’ve added the ORDER BY clause with the list of columns we want to sort the data by. As you can see, this sorts the output information based on the order of the columns listed.

Example #5: COUNT(expression)

So far, we’ve looked at simple examples of working with COUNT() ; the purpose was to count all of the rows in the source dataset or table.

However, there are more complex ways of using the COUNT–GROUP BY combination. To explain this, we’ll build a new sample dataset.

We have a table called SurveyResponses that stores answers to a survey. Some of the questions are optional, which is why there are some respondents with NULL answer values; they’ve skipped the optional questions. You can see the sample data below:

The most common usage of the COUNT function (and its default functionality, even if it’s used with GROUP BY ) is to count the number of rows. For example, if we want to count the answer types of survey questions, we can use the following query:

The output will look like this:

The outcome of this is a count of all rows that match a certain answer. It returns the aggregate number of survey answers and the type of the answer.

Let’s look at another example that might seem to generate the same results. Actually, this example has something particular and important about it; instead of using * as a parameter in our COUNT() function, we’re using COUNT() with a column name.

We will modify the query in the previous example. Instead of the * parameter for the COUNT() function, we will replace it with the Answer column. Our new query looks like this:

If we run this new query, we will see that the results returned are almost exactly the same:

We can see that the output for the NULL answer value has changed from 1 to 0. This is because the COUNT() function only takes into consideration non- NULL values when doing the aggregation.

Before we were doing COUNT(*) , which implicitly means count the rows; COUNT(Answer) will count the values in the Answer column. And because we had 1 value with NULL , it will skip those values in its calculation – returning 0 in this second scenario.

In this third variation of using the COUNT function, we will use the same query as in our previous example. This time,  though, we will add the DISTINCT keyword before the column name.

We can see in the output above that the result of this query has turned all of the positive values in the DistinctCount column to 1. Adding the DISTINCT keyword means that each time the COUNT function finds a new value it hasn’t seen before, it will add that status to its list and add a 1  to its count. However, if it finds the same status value a second time or more, it will skip counting it.

Using COUNT() with GROUP BY is only one of the many powerful features that SQL offers. If you’re eager to explore SQL further and truly master its capabilities, I encourage you to check out our comprehensive SQL Basics course . If you’re looking for SQL practice, I recommend our SQL Practice track, which has over 600 SQL practice exercises.

After taking our courses, maybe you feel like you want to find a job working with SQL. To help you prepare for your interview, we have a list of SQL interview questions that will show you some real-world examples of questions and problems in SQL.

You may also like

How Do You Write a SELECT Statement in SQL?

What Is a Foreign Key in SQL?

Enumerate and Explain All the Basic Elements of an SQL Query