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

SQL GROUP BY Command

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.

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 sex field. Here is the query.

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

The output is here

class	total_records
Four	5
Nine	1
Seven	5
Six	5
Three	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

This article is written by plus2net.com team.

plus2net.com

▼ More on getting records from table with different combinations of commands