GROUP BY for Several Columns

      
mysql>
mysql>
mysql> CREATE TABLE titles (
    ->   titleID int(11),
    ->   title varchar(100),
    ->   subtitle varchar(100),
    ->   edition tinyint(4),
    ->   publID int(11),
    ->   catID int(11),
    ->   langID int(11),
    ->   year int(11),
    ->   isbn varchar(20),
    ->   comment varchar(255),
    ->   ts timestamp,
    ->   authors varchar(255),
    ->   PRIMARY KEY  (titleID)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql>
mysql> INSERT INTO titles VALUES (1,'Linux','Installation',5,1,57,2,2000,NULL,NULL,'2005-02-28 13:34:21','Michael'),
    ->                           (2,'Excel',NULL,NULL,2,3,NULL,2000,NULL,NULL,'2005-02-28 13:34:22','David'),
    ->                           (3,'XML',NULL,NULL,1,2,NULL,1997,NULL,NULL,'2005-02-28 13:34:22','Edwards'),
    ->                           (4,'PHP',NULL,NULL,3,6,NULL,2000,NULL,NULL,'2005-02-28 13:34:22','Tom'),
    ->                           (5,'MySQL','',0,3,34,NULL,2000,'','','2005-02-28 13:34:22','Paul'),
    ->                           (6,'Java',NULL,NULL,4,34,NULL,1999,NULL,NULL,'2005-02-28 13:34:22','Tim');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql>
mysql> CREATE TABLE languages (
    ->   langID int(11) NOT NULL auto_increment PRIMARY KEY,
    ->   langName varchar(40),
    ->   ts timestamp
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>
mysql>
mysql> INSERT INTO languages VALUES (1,'english','2004-12-02 18:37:02'),
    ->                                (2,'deutsch','2004-12-02 18:37:02'),
    ->                                (3,'svensk','2004-12-02 18:37:02'),
    ->                                (4,'norsk','2004-12-02 18:37:02');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> CREATE TABLE categories (
    ->   catID int(11) NOT NULL auto_increment PRIMARY KEY ,
    ->   catName varchar(60),
    ->   parentCatID int(11),
    ->   ts timestamp
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql>
mysql> INSERT INTO categories VALUES (1,'Computer books',11,'2004-12-02 18:37:20'),
    ->                                 (2,'Databases',1,'2004-12-02 18:37:20'),
    ->                                 (3,'Programming',1,'2004-12-02 18:37:20'),
    ->                                 (4,'Relational Databases',2,'2004-12-02 18:37:20'),
    ->                                 (5,'Object-oriented databases',2,'2004-12-02 18:37:20'),
    ->                                 (6,'PHP',3,'2004-12-02 18:37:20'),
    ->                                 (7,'Perl',3,'2004-12-02 18:37:20'),
    ->                                 (8,'SQL',2,'2004-12-02 18:37:20'),
    ->                                 (9,'Children\'s books',11,'2004-12-02 18:37:20'),
    ->                                 (10,'Literature and fiction',11,'2004-12-02 18:37:20'),
    ->                                 (11,'All books',NULL,'2004-12-02 18:37:20');
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> SELECT langName, catName, COUNT(*)
    -> FROM titles, languages, categories
    -> WHERE titles.catID = categories.catID
    -> AND titles.langID = languages.langID
    -> GROUP BY langName, catName;
Empty set (0.00 sec)

mysql>
mysql>
mysql> drop table titles;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table languages;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table categories;
Query OK, 0 rows affected (0.00 sec)

mysql>

Related examples in the same category

1.	Use GROUP BY clause to list only the unique data
2.	Another GROUP BY
3.	Use GROUP BY
4.	GROUP BY with order and HAVING
5.	Use GROUP BY 2
6.	Use GROUP BY and ORDER BY together
7.	Get GROUP BY for COUNT
8.	Simple GROUP BY
9.	GROUP and sort the records
10.	GROUP value and count
11.	Grouping Data: Filtering Group Data
12.	Grouping Data: 03 Using the HAVING Clause 1
13.	Grouping Data: Using the HAVING Clause 1
14.	Using math function in HAVING
15.	GROUP and HAVING with sub query
16.	Group by and order by for linked tables
17.	Grouping by Expression Results
18.	Give the expression an alias in the output column list and refer to the alias in the GROUP BY clause
19.	Write the GROUP BY clause to refer to the output column position:
20.	Group by multiple expressions if you like.
21.	Group mail table records into categories of 100,000 bytes
22.	Group by DAYOFWEEK( )
23.	Working with Per-Group and Overall Summary Values Simultaneously
24.	Finding Rows Containing Per-Group Minimum or Maximum Values
25.	Maximum-per-group problem for this table
26.	Another way to group statements is to turn off auto-commit mode explicitly.
27.	To use a GROUP BY clause effectively, you should also include a select list element that contains a function t
28.	Specifies two columns in the GROUP BY clause
29.	Working with Grouped Data
30.	Group by calculated value
31.	Group value in subquery
32.	Group by two columns
33.	Group by then order by vs Group by only
34.	GROUP BY returns a final sum for the first column and supplementary partial sums for the second column.
35.	Dividing a Summary into Subgroups
36.	Use the name column to place the rows in groups, but the summary functions operate on the miles values:
37.	Parentheses may be used to group alternations.
38.	To be more specific and find out how many messages each sender sent from each host, use two grouping columns.
39.	Categorize groups on a logarithmic scale.
40.	Missing and non-missing counts can be determined for subgroups as well.
41.	get the number of orders per customer
42.	A less fine-grained summary can be obtained by using only the month values
43.	Find out how many books you have per author, use this query
44.	Ascertain the most common initial letter for state names like this:

GROUP BY for Several Columns : Group « Select Clause « SQL / MySQL

Related examples in the same category