Mega Code Archive

 

Categories / MySQL / Select Clause

 

Grouping by Expression Results

mysql> mysql> CREATE TABLE states     -> (     ->  name            VARCHAR(30) NOT NULL,   # state name     ->  abbrev          CHAR(2) NOT NULL,               # 2-char abbreviation     ->  statehood       DATE,                                   # date of entry into the Union     ->  pop                     BIGINT,                                 # population as of 4/1990     ->  PRIMARY KEY (abbrev)     -> ); Query OK, 0 rows affected (0.00 sec) mysql> mysql> insert into states(name, abbrev, statehood, pop)values     -> ("Alabama","AL","1819-12-14",4040587),     -> ("Alaska","AK","1959-01-03",550043),     -> ("Arizona","AZ","1912-02-14",3665228),     -> ("Arkansas","AR","1836-6-15",2350725),     -> ("California","CA","1850-9-9",29760021),     -> ("Colorado","CO","1876-8-1",3294394),     -> ("Connecticut","CT","1788-1-9",3287116),     -> ("Delaware","DE","1787-12-7",666168),     -> ("Florida","FL","1845-3-3",12937926),     -> ("Georgia","GA","1788-1-2",6478216),     -> ("Hawaii","HI","1959-08-21",1108229),     -> ("Idaho","ID","1890-7-3",1006749),     -> ("Illinois","IL","1818-12-3",11430602),     -> ("Indiana","IN","1816-12-11",5544159),     -> ("Iowa","IA","1846-12-28",2776755),     -> ("Kansas","KS","1861-1-29",2477574),     -> ("Kentucky","KY","1792-6-1",3685296),     -> ("Louisiana","LA","1812-4-30",4219973),     -> ("Maine","ME","1820-3-15",1227928),     -> ("Maryland","MD","1788-4-28",4781468),     -> ("Massachusetts","MA","1788-2-6",6016425),     -> ("Michigan","MI","1837-1-26",9295297),     -> ("Minnesota","MN","1858-5-11",4375099); Query OK, 23 rows affected (0.00 sec) Records: 23  Duplicates: 0  Warnings: 0 mysql> mysql> SELECT LENGTH(name), COUNT(*)     -> FROM states GROUP BY LENGTH(name); +--------------+----------+ | LENGTH(name) | COUNT(*) | +--------------+----------+ |            4 |        1 | |            5 |        2 | |            6 |        3 | |            7 |        5 | |            8 |        7 | |            9 |        2 | |           10 |        1 | |           11 |        1 | |           13 |        1 | +--------------+----------+ 9 rows in set (0.00 sec) mysql> mysql> drop table states; Query OK, 0 rows affected (0.00 sec)