SQL GROUP BY query to count records within a group & sub group of data

You can see the Count command before using GROUP BY command here. GROUP BY command will create groups in the field name specified and will count the number of records in the groups. This is very useful command.

Video Tutorial on SQL GROUP BY

We can also use WHERE command along with GROUP BY command in Mysql tables.

SELECT count(*) as total_records, class  FROM `student` group by class

This will display the total records in each class. Like this

total_records	class
1	Eight
3	Five
9	Four
2	Nine
10	Seven
7	Six
3	Three

Let us try to get the total number of girls records in each class by using GROUP BY query. Here we want to filter our query for only girls so we have to use one WHERE clause to restrict the records using the gender field. Here is the query.

SELECT class,count( * ) AS total_records  FROM `student` WHERE gender='female' GROUP BY class

The output is here

class	total_records
Four	5
Nine	1
Seven	5
Six	5
Three	1

GROUP BY with COUNT(), MIN(),MAX(),AVG()

SELECT class, count(*) no, AVG(mark),MAX(mark),MIN(mark),SUM(mark)
 FROM student GROUP BY class ORDER BY no DESC

class	no	AVG(mark)	MAX(mark)	MIN(mark)	SUM(mark)
Seven	10	77.6000	90	55	776
Four	9	70.8889	88	55	638
Six	7	82.5714	96	54	578
Three	3	73.6667	85	55	221
Five	3	79.3333	85	75	238
Nine	2	41.5000	65	18	83
Eight	1	79.0000	79	79	79

Using two columns with GROUP BY

SELECT class,gender, COUNT( * ) No FROM student
 GROUP BY class, gender order by class

Output

class	gender	No
Eight	male	1
Five	male	3
Four	female	5
Four	male	4
Nine	female	1
Nine	male	1
Seven	female	5
Seven	male	5
Six	female	5
Six	male	2
Three	female	1
Three	male	2

GROUP BY with CASE

We can further break the gender column to get number of male and female in each class by using SQL case.

SELECT class, count(*) as Total,
sum(CASE WHEN gender ='male' THEN 1 ELSE 0 END) as Male,
sum(CASE WHEN gender ='Female' THEN 1 ELSE 0 END) as Female
 FROM student group by class;

Output

class	Total	Male	Female
Eight	1	1	0
Five	3	3	0
Four	9	4	5
Nine	2	1	1
Seven	10	5	5
Six	7	2	5
Three	3	2	1

Duplicate records

We can find out duplicate records in a table by using group by command. We will create another table student2_1 by using same data and by adding some duplicate records. Here is the query

SELECT name, COUNT(id) AS no from student2_1 group by name having  no > 1

Output is here .

name	no
Arnold	2
Tade Row	3

You can download the SQL dump of this stduent2_1 table with added duplicate data at the end of this page.

How to delete duplicate records

By using group by on a date field we can generate list on calendar year, financial year, month wise

Sample code

Here is a sample code to understand how to use group by query using PHP script and PDO.

require "../config.php"; // Database connection 
///////////////////////////
$sql="SELECT count(*) as total_records, class FROM `student` group by class ";
echo "<table>";
foreach ($dbo->query($sql) as $row) {
echo "<tr ><td>$row[class]</td><td>$row[total_records]</td></tr>";
}
echo "</table>";

PART II: We will try to apply group by command more than one fields. For this we will create a new table and discuss in detail. Next Part II

Down load the SQL DUMP of this student table
Down load the SQL DUMP of stduent2_1 table
SQL Math References GROUP_CONCAT() Group by command for more than one column

sampat	26-02-2009
i want to display the all record from table, in which if column contain duplicate value then it will show duplicate value at once

smo	27-02-2009
You have to use distinct SQL command. Read here on how to use Distinct to get unique records.

alicia	22-08-2009
very nice tutorial it really helps me a lot :)

karthik	23-10-2010
i want to display a column into row.

SQL GROUP BY Command

Video Tutorial on SQL GROUP BY

GROUP BY with COUNT(), MIN(),MAX(),AVG()

Using two columns with GROUP BY

GROUP BY with CASE

Duplicate records

Sample code