How to use Group by in MySQL Or MariaDB To Group Data - MySQL Developer Tutorial

How to use Group by in MySQL Or MariaDB To Group Data

You are working as MySQL Developer. You have a Customer table which has CountryShortName and SaleAmount. You are asked to write a query that should return Sum of SaleAmount , count of records by CountryShortName.

Solution:

Group by clause is often used with aggregate functions such as Sum, Avg,Count,Max,Min to group the result set by column/s.

Let's create our sample table with some data and write our query with Group by to answer our question.

Create table Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  SaleAmount Int);

-- Insert Rows in Customer Table
insert into dbo.Customer
Values (
1,'Raza','M','PK',10),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87),
(6,'John',Null,'US',Null);

Let's write our query by using Sum, Count and Group by Clause

SELECT Sum(saleamount) AS TotalSaleByCountry, 
       Count(*) AS RecordCountByCountry, 
       countryshortname 
FROM   customer 
GROUP  BY countryshortname;

How to use Group by Clause in MySQL or MariaDB

You can also use multiple columns in group by clause. think about if our table would have states and you would like to group by CountryShortName and State, You would simple include State in query as shown below.

SELECT Sum(saleamount) AS TotalSaleByCountry, 
       Count(*) AS RecordCountByCountry, 
       countryshortname,
       [State]
FROM   customer 
GROUP  BY countryshortname,State;