DbSchema Database Designer


Publish on DbSchema Blog >>>

In the realm of databases and data management, SQL is a tool that plays an indispensable role. While SQL serves numerous purposes, one of its key functionalities is the ability to group data in meaningful ways using the GROUP BY statement. This tutorial will provide you with an in-depth understanding of this feature.

What is “Grouping” in SQL, and Why is it Needed?

Imagine a database filled with vast amounts of raw, unorganized data. Without any means to structure this data, the retrieval of meaningful information becomes a herculean task. Grouping in SQL is analogous to categorizing similar items in real life into separate baskets. By using the GROUP BY clause, one can group rows that have the same values in specified columns into summary rows, making data more meaningful and easier to interpret.

For example, imagine a retailer wanting to understand the total sales per product category. Grouping can help in summarizing this data efficiently.

Difference Between GROUP BY and ORDER BY

Used to group rows that have the same values in specified columns. Used to sort the result set in ascending or descending order.
Can change the number of rows in the result (due to aggregation). Doesn’t change the number of rows, only rearranges them.

GROUP BY Columns


SELECT column1, column2, ...
FROM table_name
GROUP BY column1, column2, ...;

Use SQL GROUP BY on a Single Column:

Sample Database Table - Sales

Product Amount Region
A 10 North
B 15 North
A 20 South
C 30 East
B 5 South


SELECT Product, SUM(Amount) as TotalAmount
FROM Sales
GROUP BY Product;


Product TotalAmount
A 30
B 20
C 30


The query aggregates sales by product, summing up the total amount for each product.

Use SQL GROUP BY on Multiple Columns:


SELECT Product, Region, SUM(Amount) as TotalAmount
FROM Sales
GROUP BY Product, Region;


Product Region TotalAmount
A North 10
A South 20
B North 15
B South 5
C East 30


The sales are grouped by both product and region, providing a breakdown of sales amounts for each combination.



SELECT Product, SUM(Amount) as TotalAmount
FROM Sales
GROUP BY Product
ORDER BY TotalAmount DESC;


Product TotalAmount
A 30
C 30
B 20


The products are listed by their total sales amount in descending order.

GROUP BY User-defined Functions, Column Expressions, and HAVING:

Functions and column expressions allow you to customize your data before grouping. The HAVING clause is like WHERE but works after grouping.


SELECT Product, SUM(Amount) as TotalAmount
FROM Sales
GROUP BY Product
HAVING TotalAmount > 20;


Product TotalAmount
A 30
C 30


Products with total sales amounts exceeding 20 are displayed.


Join tables and then group the results.

Sample Database Table - ProductInfo

Product Category
A Electronics
B Apparel
C Groceries


SELECT Category, SUM(Amount) as TotalAmount
FROM Sales
JOIN ProductInfo ON Sales.Product = ProductInfo.Product
GROUP BY Category;


Category TotalAmount
Electronics 30
Apparel 20
Groceries 30


By joining the Sales and ProductInfo tables, this query gives the total sales amount for each product category.

GROUP BY with Aggregations:

Use aggregation functions like COUNT, SUM, AVG.


SELECT Product, COUNT(Product) as NumberOfSales, AVG(Amount) as AverageSale
FROM Sales
GROUP BY Product;


Product NumberOfSales AverageSale
A 2 15
B 2 10
C 1 30


For each product, the query displays the number of sales transactions and the average sale amount.

Common Mistakes:

  1. Not Including Non-aggregated Columns in GROUP BY: Every column in your SELECT that isn’t used with an aggregation function needs to be in the GROUP BY clause.
  2. Misusing HAVING: Remember, HAVING is applied after GROUP BY, not before. For filtering before, use WHERE.


  1. Can I use GROUP BY without any aggregation functions?
    Yes, but usually, GROUP BY is used in conjunction with aggregations to summarize data.

  2. Why are my results unexpected with GROUP BY?
    Ensure every non-aggregated column in your SELECT is also in your GROUP BY clause.

Practice Questions:

  1. Write a SQL statement to find the total amount of sales for each region.
  2. Find the average sale amount for each product category.
  3. How many unique products have been sold in each region?
  4. List categories with total sales more than 50.


In conclusion, the GROUP BY statement in SQL provides a potent tool for summarizing and organizing data. By mastering its various nuances and functionalities, one can efficiently dissect and interpret vast amounts of data. We hope this guide serves as a valuable resource in your SQL journey!

Remember: Consistent practice and real-world application are key to mastering SQL GROUP BY and other SQL functionalities.

Happy Querying !!

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.

Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.

Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.

Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.

SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.

Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.

Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.

Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.

DbSchema can be downloaded for free. No registration is required.