MySQL Wildcards

MySQL Wildcards

MySQL Aliases

MySQL Aliases

MySQL Data Types

MySQL Data Types

MySQL Interview Questions

MySQL Interview Questions and Answers


MySQL Group By Clause

The GROUP BY clause groups data from multiple record and returns one row for each group. On other hand, it reduces the number of rows in the result set. It is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns. The GROUP BY clause must appear after the WHERE clauses.

Note: The MySQL ORDER BY clause can be used with SELECT statement, SELECT LIMIT statement, and DELETE LIMIT statement.

MySQL GROUP BY CLAUSE Syntax

To group the data from multiple record into one row for each group, use the following syntax:

Syntax

SELECT col1, col2, ... FROM tablename WHERE condition 
GROUP BY col1, col2,...

Parameters:

tablename: The table name from which you want to fetch the records.

condition: To specify the conditions that must be fulfilled for the records to be selected.

GROUP BY: To sorts data set in descending order.

MySQL GROUP BY CLAUSE example

The following MySQL statement is used to group "cellNo" from "tblCustomerLoan" table:

Example

SELECT firstName, lastName, address, cellNo FROM tblCustomerLoan Group By cellNo;

Note:

In the above example, we have fetched records from "tblCustomerLoan" table with GROUP BY on "cellNo". As you can see, the GROUP BY clause returns unique occurrences of "cellNo" values.

You can use MySQL Command Line Client to fetched records from "tblCustomerLoan" table with GROUP BY on "cellNo". It will look like this:

MySQL GROUP BY CLAUSE example with COUNT function

The following MySQL statement is used to group "cellNo" and count repetitive "cellNo" in the column "cellNo" from "tblCustomerLoan" table:

Example

SELECT firstName, lastName, address, count(cellNo) AS cellNo FROM tblCustomerLoan Group By cellNo;

Note:

In the above example, we have grouped "cellNo" and count repetitive "cellNo" in the column "cellNo" from "tblCustomerLoan" table. As you can see, the GROUP BY clause returns count for each repetitive "cellNo".

You can use MySQL Command Line Client to fetch records from "tblCustomerLoan" table with GROUP BY and count() function on "cellNo". It will look like this:

MySQL GROUP BY CLAUSE example with COUNT function & JOIN

The following MySQL statement is used to group "banks" name and count repetitive "banks" name in the column "banks" from "tblbankdemataccountcharges" table:

Example

SELECT B.banks, A.accountName, count(B.banks) AS Count FROM tblbankdemataccountcharges A 
Inner Join tblbankheadquartersandaddress B GROUP BY B.banks LIMIT 15;

Note:

In the above example, we have grouped "banks" name and count repetitive "banks" name in the column "banks" from "tblbankdemataccountcharges" table with INNER JOIN "tblbankheadquartersandaddress" table. As you can see, the GROUP BY clause returns count for each repetitive "banks" name.

You can use MySQL Command Line Client to fetch records from "tblbankdemataccountcharges" table with GROUP BY, count() function and INNER JOIN on "banks" column. It will look like this: