SQL PHP HTML ASP JavaScript articles and free scripts to download
 
 

SQL GROUP BY Command for more than one field with sub grouping

Please read basic of group by command Part I
We know by using count command and group by command we can find out the total records in a particular group of data. Now we will try to apply group by command including two different fields and we will break the groups and get total records for each sub-group also. This way we will get total of each subgroup also. This can be further improved by adding SQL HAVING command with the group by command.

For this example you can download the query file to create the table with some data inside it. Inside this table there are records of documents saying details of the records. The records belongs to one category( management or Computers) and then belong to one type ( Book, CD or report )
Here is the full table with all the rows. On this we will apply the group by query
d_id name type category price stock
1 Book1 Book Management 220 10
2 Book2 CD Management 120 8
3 Book3 Report Management 25 4
4 Book4 Book Management 55 8
5 Document1 Book Management 15 23
6 Document2 CD Computers 80 45
7 Document3 Report Computers 55 65
8 Book5 Report Management 80 10
9 Document4 CD Management 72 5
10 Book 8 Book Computers 88 6
11 Book 9 CD Computers 100 5
12 Document5 Report Computers 85 8
13 Book 10 Book Computers 150 5
Here is the SQL group by command applied on the above table.

SELECT category,type, count(*) as total FROM `documents` group by category,type

This will display the total records in each group and sub group. Like this

category type total
Computers Book 2
Computers CD 2
Computers Report 2
Management Book 3
Management CD 2
Management Report 2

Part I Learn the basic of sql group by command

Download the SQL group by query file to create the table with data

Discuss this tutorial at forum

List of SQL Tutorials


 
Scripts
PHP
JavaScript
HOME
SQL Tutorial List
SQL (Home)
SQL Commands
AVG
Alter Table
Between
Count
Copy Table
Create Table
Delete
Distinct
Group by
Having
Insert
Inner Join
IN
Left join
Limit
Like
MAX
MIN
Order By
OR AND
Rand
Replace
Rename Table
Select Query
Sum
Union
Update
Where
Subscribe
Submit your email address and receive article and product notifications. Your email is safe with us.