The SQL GROUP BY Statement
The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".
The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.
-----------
GROUP BY Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
--------------
| 353 | 5 |
| 357 | 3 |
| 362 | 3 |
| 363 | 3 |
| 379 | 3 |
| 381 | 4 |
| 382 | 4 |
| 385 | 3 |
| 386 | 3 |
| 398 | 4 |
| 406 | 3 |
| 412 | 3 |
| 415 | 1 |
| 424 | 3 |
| 447 | 3 |
| 448 | 3 |
| 450 | 4 |
| 452 | 3 |
| 455 | 2 |
| 456 | 2 |
| 458 | 3 |
| 462 | 3 |
| 471 | 3 |
| 473 | 2 |
| 475 | 2 |
| 484 | 2 |
| 486 | 3 |
| 487 | 2 |
| 489 | 2 |
| 495 | 2 |
| 496 | 4 |
+----------------+------------------+
98 rows in set (0.001 sec)
MariaDB [classicmodels]> select customernumber,count(customernumber) as "number of orders" from orders group by customernumber
-> order by count(customernumber);
+----------------+------------------+
| customernumber | number of orders |
+----------------+------------------+
| 415 | 1 |
| 260 | 2 |
| 219 | 2 |
| 473 | 2 |
| 227 | 2 |
| 189 | 2 |
| 475 | 2 |
| 484 | 2 |
| 239 | 2 |
| 286 | 2 |
| 240 | 2 |
| 171 | 2 |
| 298 | 2 |
| 202 | 2 |
| 487 | 2 |
| 299 | 2 |
| 204 | 2 |
| 339 | 2 |
| 489 | 2 |
| 249 | 2 |
| 173 | 2 |
| 455 | 2 |
| 344 | 2 |
| 495 | 2 |
| 456 | 2 |
| 347 | 2 |
| 256 | 2 |
| 177 | 2 |
| 319 | 2 |
| 211 | 2 |
| 259 | 2 |
| 216 | 3 |
| 412 | 3 |
| 186 | 3 |
| 471 | 3 |
| 357 | 3 |
| 187 | 3 |
| 362 | 3 |
| 278 | 3 |
| 424 | 3 |
| 324 | 3 |
| 233 | 3 |
| 363 | 3 |
| 447 | 3 |
| 198 | 3 |
| 328 | 3 |
| 129 | 3 |
| 379 | 3 |
| 167 | 3 |
| 448 | 3 |
| 201 | 3 |
| 333 | 3 |
| 486 | 3 |
| 334 | 3 |
| 242 | 3 |
| 172 | 3 |
| 452 | 3 |
| 385 | 3 |
| 311 | 3 |
| 103 | 3 |
| 205 | 3 |
| 250 | 3 |
| 386 | 3 |
| 175 | 3 |
| 314 | 3 |
| 112 | 3 |
| 209 | 3 |
| 146 | 3 |
| 458 | 3 |
| 350 | 3 |
| 406 | 3 |
| 181 | 3 |
| 320 | 3 |
| 462 | 3 |
| 119 | 4 |
| 151 | 4 |
| 321 | 4 |
| 121 | 4 |
| 157 | 4 |
| 276 | 4 |
| 161 | 4 |
| 128 | 4 |
| 166 | 4 |
| 282 | 4 |
| 131 | 4 |
| 381 | 4 |
| 450 | 4 |
| 382 | 4 |
| 144 | 4 |
| 496 | 4 |
| 398 | 4 |
| 353 | 5 |
| 323 | 5 |
| 145 | 5 |
| 114 | 5 |
| 148 | 5 |
| 124 | 17 |
| 141 | 26 |
+----------------+------------------+
98 rows in set (0.029 sec)
MariaDB [classicmodels]> select customernumber,count(customernumber) as "number of orders" from orders group by customernumber
-> order by count(customernumber) desc;
+----------------+------------------+
| customernumber | number of orders |
+----------------+------------------+
| 141 | 26 |
| 124 | 17 |
| 353 | 5 |
| 323 | 5 |
| 145 | 5 |
| 114 | 5 |
| 148 | 5 |
| 119 | 4 |
| 151 | 4 |
| 321 | 4 |
| 121 | 4 |
| 157 | 4 |
| 276 | 4 |
| 161 | 4 |
| 128 | 4 |
| 166 | 4 |
| 282 | 4 |
| 131 | 4 |
| 381 | 4 |
| 450 | 4 |
| 382 | 4 |
| 144 | 4 |
| 496 | 4 |
| 398 | 4 |
| 216 | 3 |
| 412 | 3 |
| 186 | 3 |
| 471 | 3 |
| 357 | 3 |
| 187 | 3 |
| 362 | 3 |
| 278 | 3 |
| 424 | 3 |
| 324 | 3 |
| 233 | 3 |
| 363 | 3 |
| 447 | 3 |
| 198 | 3 |
| 328 | 3 |
| 129 | 3 |
| 379 | 3 |
| 167 | 3 |
| 448 | 3 |
| 201 | 3 |
| 333 | 3 |
| 486 | 3 |
| 334 | 3 |
| 242 | 3 |
| 172 | 3 |
| 452 | 3 |
| 385 | 3 |
| 311 | 3 |
| 103 | 3 |
| 205 | 3 |
| 250 | 3 |
| 386 | 3 |
| 175 | 3 |
| 314 | 3 |
| 112 | 3 |
| 209 | 3 |
| 146 | 3 |
| 458 | 3 |
| 350 | 3 |
| 406 | 3 |
| 181 | 3 |
| 320 | 3 |
| 462 | 3 |
| 260 | 2 |
| 219 | 2 |
| 473 | 2 |
| 227 | 2 |
| 189 | 2 |
| 475 | 2 |
| 484 | 2 |
| 239 | 2 |
| 286 | 2 |
| 240 | 2 |
| 171 | 2 |
| 298 | 2 |
| 202 | 2 |
| 487 | 2 |
| 299 | 2 |
| 204 | 2 |
| 339 | 2 |
| 489 | 2 |
| 249 | 2 |
| 173 | 2 |
| 455 | 2 |
| 344 | 2 |
| 495 | 2 |
| 456 | 2 |
| 347 | 2 |
| 256 | 2 |
| 177 | 2 |
| 319 | 2 |
| 211 | 2 |
| 259 | 2 |
| 415 | 1 |
+----------------+------------------+
98 rows in set (0.001 sec)
MariaDB [classicmodels]> select customernumber,count(customernumber) as "number of orders" from orders group by customernumber
-> order by count(customernumber) desc limit 1;
+----------------+------------------+
| customernumber | number of orders |
+----------------+------------------+
| 141 | 26 |
+----------------+------------------+
1 row in set (0.001 sec)
MariaDB [classicmodels]> show tables;
+-------------------------+
| Tables_in_classicmodels |
+-------------------------+
| customers |
| employees |
| offices |
| orderdetails |
| orders |
| payments |
| productlines |
| products |
+-------------------------+
8 rows in set (0.001 sec)
MariaDB [classicmodels]> desc customers;
+------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------+------+-----+---------+-------+
| customerNumber | int(11) | NO | PRI | NULL | |
| customerName | varchar(50) | NO | | NULL | |
| contactLastName | varchar(50) | NO | | NULL | |
| contactFirstName | varchar(50) | NO | | NULL | |
| phone | varchar(50) | NO | | NULL | |
| addressLine1 | varchar(50) | NO | | NULL | |
| addressLine2 | varchar(50) | YES | | NULL | |
| city | varchar(50) | NO | | NULL | |
| state | varchar(50) | YES | | NULL | |
| postalCode | varchar(15) | YES | | NULL | |
| country | varchar(50) | NO | | NULL | |
| salesRepEmployeeNumber | int(11) | YES | MUL | NULL | |
| creditLimit | decimal(10,2) | YES | | NULL | |
+------------------------+---------------+------+-----+---------+-------+
13 rows in set (0.060 sec)
MariaDB [classicmodels]> select * from customers where customernumber =141;
+----------------+------------------------+-----------------+------------------+----------------+--------------------+--------------+--------+-------+------------+---------+-----------------
-------+-------------+
| customerNumber | customerName | contactLastName | contactFirstName | phone | addressLine1 | addressLine2 | city | state | postalCode | country | salesRepEmployee
Number | creditLimit |
+----------------+------------------------+-----------------+------------------+----------------+--------------------+--------------+--------+-------+------------+---------+-----------------
-------+-------------+
| 141 | Euro+ Shopping Channel | Freyre | Diego | (91) 555 94 44 | C/ Moralzarzal, 86 | NULL | Madrid | NULL | 28034 | Spain |
1370 | 227600.00 |
+----------------+------------------------+-----------------+------------------+----------------+--------------------+--------------+--------+-------+------------+---------+-----------------
-------+-------------+
1 row in set (0.029 sec)
MariaDB [classicmodels]> select phone from customers where customernumber =141;
+----------------+
| phone |
+----------------+
| (91) 555 94 44 |
+----------------+
1 row in set (0.001 sec)
MariaDB [classicmodels]>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.