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

MySQL Group by Query 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.
Related Tutorial Group by SQL Having


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


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


plus2net.com


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.

Post your comments , suggestion , error , requirements etc here .










SQL Popular Tutorials

 Order by displaying records Records Between two date ranges Some of the records by LIMIT query SQL SUM of data in columns
✖
We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2020 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer