SQL GROUP BY Multiple Columns and Subgroups

MySQL GROUP BY query with multiple columns

GROUP BY can group by more than one column. Each unique combination becomes its own subgroup. Here we group documents by both category and type, then count the rows in each combination. Use HAVING when you need to filter the grouped result.

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
ORDER 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

Download the SQL group by query file to create the table with data
SQL References Part I Learn the basic of sql group by command

GROUP BY Examples HAVING GROUP_CONCAT()

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

Floris	13-09-2011
Hi, I hope somebody can help. I have done a simmilar thing as the above. I used it to find duplicates based on several collumns by adding count* > 1. But now i want to show rows that are in these groups. How can I do that?

khan150	21-09-2011
Floris, suppose you are trying to find dup records in the table above. This should help SELECT d_id, count(d_id) as numofoccurrence FROM documents GROUP BY d_id HAVING count(d_id) > 1 this will display the d_id of the record and number of times its repeating. if you want to see the entire record, add more columns after select and group by respectively

Ambas	02-03-2013
if can want to ask, i have 2 tables, in the have the same name of field and how i can call form text data to the combobox with php? thanks.

smo123	11-04-2013
Along with table name you can call the columns like this. select table1.name1, table2.name1 from table1, table2 where table1.id=table2.id Here both tables have same column name name1 and id

Vineet	11-08-2014
This is very helpful information to read in short time. Thanks to author.

GROUP BY with Multiple Columns

Subhendu Mohapatra