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.

GROUP BY SQL query to get number of records, average , maximum minimum & sum over a group of records

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_recordsclass

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

class total_records
Four5
Nine1
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

classnoAVG(mark)MAX(mark)MIN(mark)SUM(mark)
Seven1077.60009055776
Four970.88898855638
Six782.57149654578
Three373.66678555221
Five379.33338575238
Nine241.5000651883
Eight179.0000797979

Using two columns with GROUP BY

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

Output

classgenderNo
Eightmale1
Fivemale3
Fourfemale5
Fourmale4
Ninefemale1
Ninemale1
Sevenfemale5
Sevenmale5
Sixfemale5
Sixmale2
Threefemale1
Threemale2

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

classTotalMaleFemale
Eight110
Five330
Four945
Nine211
Seven1055
Six725
Three321

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 .

nameno
Arnold2
Tade Row3

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>";

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

→

←

Subscribe to our YouTube Channel here