AppDividend
Latest Code Tutorials

SQL Group By Example | Group By Clause In SQL Tutorial

0

SQL Group By Example | Group By Clause In SQL Tutorial is today’s topic. The GROUP BY clause in SQL is used to arrange the same data into groups with the help of some functions. The GROUP BY clause group rows that have the same values into summary rows, like “find the number of customers in each city.” The Group By statement allows you to arrange the rows of a query in the groups. The groups are determined by the table columns that you specify in a GROUP BY clause.

SQL Group By Example

The GROUP BY statement is also used with the aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result by one or more columns. Its main work is to summarize the data from the database. If you want to build some kind of chart then the GROUP BY clause will be helpful to fetch the summarize the data and then draw the chart based on the fetched data. The queries that contain the GROUP BY clause are called grouped queries and only return a single row for every grouped item.

Key Points

  1. GROUP BY statement is used with a SELECT statement.
  2. In the query, GROUP BY statement is placed after the WHERE clause.
  3. In the query, GROUP BY statement is placed before ORDER BY clause if used any.

Syntax

SELECT
    select_columns
FROM
    table
GROUP BY
    column_name1,
    column_name2 ,...;

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

Now, let’s go to the example.

First, we need to create two tables. If you do not know how to create the table in SQL, then check out SQL Create Table tutorial. Now, create the first table.

We have our products dummy data. See the below data.

SQL Group By Example

 

 

We have the products table which has ProductID, ProductName, ProductPrice, and created_at columns.

Consider the following GROUP BY example.

SELECT
    ProductName,
    YEAR (created_at) OrderYear
FROM
    Products

See the below output.

 

Group By Clause In SQL Tutorial

 

We have selected the ProductName and just Year from the date.

We can add further constraints like Order By. See the following query.

SELECT
    ProductID,
    ProductName,
    YEAR (created_at) OrderYear
FROM
    Products
ORDER BY
    ProductID DESC

See the following example.

SQL GROUP BY Statement

 

In this example, we retrieved the ProductID, ProductName, and the ordered year of the Products as you can see clearly from the outcome, the Products with the ProductID Order by descending.

Now, see the following query.

SELECT
    ProductID,
    YEAR (created_at) OrderYear,
    ProductPrice
FROM
    Products
GROUP BY
    ProductPrice
ORDER BY
    ProductPrice DESC

See the below output.

SQL GROUP BY Statement

 

 

In the above query, we have selected three columns, which are Group By ProductPrice.

We have used the GROUP By and ORDER BY clause.

Now, let’s modify the database. We will add some more data.

See the new database.

MySQL GROUP BY

 

 

 

Let’s use the count aggregate function to count the number of items example.

See the below example.

SELECT
    COUNT(ProductID) as TotalID,
    ProductName,
    Year(created_at)
FROM
    Products
GROUP BY
    ProductName
ORDER BY
    created_at

In the above query, we have selected three columns.

We are selecting the no. of ids which have the same number of Products.

So, we have used GROUP BY clause with ProductName column name. Thus, the count function will count the no. of rows which has the same products and gives the total and print in the output. See the below output.

SQL GROUP BY Examples

 

In the above output, you can see that the FRONTROW SNEAKER product has appeared two times, LV INITIALS looks only one time. 

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

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

If you want to refer to any other column or expression that is not listed in the GROUP BY statement, you must use that column as an input of the aggregate function. Otherwise, you will get the error because there is no guarantee that the column or expression will return the single value per group.

Using GROUP BY clause with the MIN and MAX functions example

See the following query.

SELECT
    ProductID,
    COUNT(ProductID) as Total,
    ProductName,
    MIN(ProductPrice) as min_price
FROM    
    Products
GROUP BY
    ProductName
ORDER BY
    ProductID

In the above query, we are selecting four columns. We are selecting a group by ProductName and count the total products with its minimum price. See the below output.

Using GROUP BY clause with the MIN and MAX functions example

 

Conclusively, SQL Group By Example | Group By Clause In SQL Tutorial is over.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.