GROUP value and count : Group « Select Clause « SQL / MySQL

Home
SQL / MySQL
1.Aggregate Functions
2.Backup Load
3.Command MySQL
4.Cursor
5.Data Type
6.Database
7.Date Time
8.Engine
9.Event
10.Flow Control
11.FullText Search
12.Function
13.Geometric
14.I18N
15.Insert Delete Update
16.Join
17.Key
18.Math
19.Procedure Function
20.Regular Expression
21.Select Clause
22.String
23.Table Index
24.Transaction
25.Trigger
26.User Permission
27.View
28.Where Clause
29.XML
SQL / MySQL » Select Clause » Group 
GROUP value and count
   
/*
mysql> select * from employee_person;
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
| id | address                             | phone   | email | birthday   | sex  | m_status | s_name          | children |
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
|  1 | 200, Regina Street                  | 7176666 | [email protected] | 1971-04-26 | M    | Y        | Ane Regina      |     NULL |
|  2 | 1232 Alberta Road                   | 5553312 | [email protected] | 1968-03-02 | M    | Y        | Jane Van        |        3 |
|  3 | 90 Potter A                         | 3331211 | [email protected] | 1967-09-22 | M    | N        | Sandhya Pil     |        2 |
|  4 | 202, Donna Street                   | 7176167 | [email protected] | 1976-08-09 | F    | Y        | Manish Sha      |     NULL |
|  5 | Apartment #8, UBC, Van Street       | 8973242 | [email protected] | 1974-10-14 | F    | N        | NULL            |     NULL |
|  6 | 46 SFU Street                       | 6451234 | [email protected] | 1978-12-31 | M    | N        | NULL            |     NULL |
|  7 | 432 Mercury Ave                     | 7932232 | [email protected] | 1966-08-21 | M    | Y        | Mary Shelly     |        3 |
|  8 | 8 Little YaleTown                   | 5442994 | [email protected] | 1975-01-14 | M    | N        | NULL            |     NULL |
|  9 | 64 Temp Road                        | 4327652 | [email protected] | 1969-05-19 | M    | Y        | Man Nanda       |        1 |
| 10 | 132 Metro House, Henry Street       | 5552376 | [email protected] | 1968-07-06 | M    | N        | NULL            |     NULL |
| 11 | 1 Grace Town, Van Avenue            | 5433879 | [email protected] | 1957-11-04 | M    | Y        | Muriel Lovelace |        4 |
| 12 | 97 Oakland Road                     | 5423311 | [email protected] | 1968-02-15 | M    | Y        | Rina Brighton   |        3 |
| 13 | 543 Applegate Lane                  | 3434343 | [email protected] | 1968-09-03 | F    | Y        | Matt Shi        |        2 |
| 14 | 76 Fish Street                      | 7432433 | [email protected] | 1965-04-28 | M    | N        | NULL            |     NULL |
| 15 | 98 Gun Street                       | 6500787 | [email protected] | 1966-06-23 | M    | Y        | Betty Cudly     |        3 |
| 16 | #5 Winnepag Homes                   | 5433243 | [email protected] | 1964-03-06 | M    | Y        | Stella Stevens  |        2 |
| 17 | 652 Devon Building, 6th Jade Avenue | 5537885 | [email protected] | 1970-04-18 | F    | Y        | Edgar Alan      |        1 |
| 18 | Apartment #9, Together Towers       | 5476565 | [email protected] | 1973-10-09 | M    | N        | NULL            |     NULL |
| 19 | Apartment #9, West Towers           | 5476565 | [email protected] | 1973-01-20 | M    | N        | NULL            |     NULL |
| 20 | 90 Yale Town                        | 7528326 | [email protected] | 1968-01-25 | F    | N        | NULL            |     NULL |
| 21 | 4329 Eucalyptus Avenue              | 4254863 | [email protected] | 1964-06-13 | M    | Y        | Ruby Richer     |        2 |
+----+-------------------------------------+---------+---------------------------+------------+------+----------+-----------------+----------+
21 rows in set (0.01 sec)

mysql> select sex, count(*)
    ->        from employee_person
    ->        GROUP BY sex;
+------+----------+
| sex  | count(*) |
+------+----------+
| M    |       16 |
| F    |        5 |
+------+----------+
2 rows in set (0.00 sec)


*/       
Drop table employee_person;

CREATE TABLE employee_person (
    id int unsigned not null primary key, 
    address varchar(60), 
    phone int, 
    email varchar(60), 
    birthday DATE, 
    sex ENUM('M', 'F'), 
    m_status ENUM('Y','N'), 
    s_name varchar(40), 
    children int
);


INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (1, '200, Regina Street', 7176666, 'ne[email protected]', '1971-04-26', 'M', 'Y', 'Ane Regina');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (2, '1232 Alberta Road', 5553312, 'jo@hotmail.com', '1968-03-02', 'M', 'Y', 'Jane Van', 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (3, '90 Potter A', 3331211, 'gp@ymail.com', '1967-09-22', 'M', 'N', 'Sandhya Pil', 2);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name) values (4, '202, Donna Street', 7176167, 'tw[email protected]', '1976-08-09', 'F', 'Y', 'Manish Sha');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (5, 'Apartment #8, UBC, Van Street', 8973242, 'ho[email protected]', '1974-10-14', 'F', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (6, '46 SFU Street', '6451234', 'ki[email protected]', '1978-12-31', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (7, '432 Mercury Ave', 7932232, 'ma[email protected]', '1966-8-21', 'M', 'Y', 'Mary Shelly', '3');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (8, '8 Little YaleTown', 5442994, 'ed[email protected]', '1975-01-14', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (9, '64 Temp Road', 4327652, 'na[email protected]', '1969-05-19', 'M', 'Y', 'Man Nanda', '1');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (10, '132 Metro House, Henry Street', 5552376, 'ra@hotmail.com', '1968-07-06', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (11, '1 Grace Town, Van Avenue', 5433879, 'so[email protected]', '1957-11-04', 'M', 'Y', 'Muriel Lovelace', '4');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (12, '97 Oakland Road', 5423311, 'ki[email protected]', '1968-02-15', 'M', 'Y', 'Rina Brighton', 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (13, '543 Applegate Lane', 3434343, 'le[email protected]', '1968-09-03', 'F', 'Y', 'Matt Shi', '2');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (14, '76 Fish Street', 7432433, 'ti[email protected]', '1965-04-28', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (15, '98 Gun Street', 6500787, 'da[email protected]', '1966-06-23', 'M', 'Y', 'Betty Cudly', 3);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (16, '#5 Winnepag Homes', 5433243, 'mi[email protected]', '1964-03-06', 'M', 'Y', 'Stella Stevens', 2);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (17, '652 Devon Building, 6th Jade Avenue', 5537885, 'mo[email protected]', '1970-04-18', 'F', 'Y', 'Edgar Alan', 1);
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (18, 'Apartment #9, Together Towers', 5476565, 'od[email protected]', '1973-10-09', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (19, 'Apartment #9, West Towers', 5476565, 'ji[email protected]', '1973-1-20', 'M', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status) values (20, '90 Yale Town', 7528326, 'he[email protected]', '1968-01-25', 'F', 'N');
INSERT INTO employee_person (id, address, phone, email, birthday, sex, m_status, s_name, children) values (21, '4329 Eucalyptus Avenue', 4254863, 'mo[email protected]', '1964-06-13', 'M', 'Y', 'Ruby Richer', 2);
  
select * from employee_person;  
  
select sex, count(*)
       from employee_person 
       GROUP BY sex;


           
         
    
    
  
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.Grouping Data: Filtering Group Data
11.Grouping Data: 03 Using the HAVING Clause 1
12.Grouping Data: Using the HAVING Clause 1
13.Using math function in HAVING
14.GROUP and HAVING with sub query
15.Group by and order by for linked tables
16.Grouping by Expression Results
17.Give the expression an alias in the output column list and refer to the alias in the GROUP BY clause
18.Write the GROUP BY clause to refer to the output column position:
19.Group by multiple expressions if you like.
20.Group mail table records into categories of 100,000 bytes
21.Group by DAYOFWEEK( )
22.Working with Per-Group and Overall Summary Values Simultaneously
23.Finding Rows Containing Per-Group Minimum or Maximum Values
24.Maximum-per-group problem for this table
25.Another way to group statements is to turn off auto-commit mode explicitly.
26.To use a GROUP BY clause effectively, you should also include a select list element that contains a function t
27.Specifies two columns in the GROUP BY clause
28.Working with Grouped Data
29.Group by calculated value
30.Group value in subquery
31.Group by two columns
32.Group by then order by vs Group by only
33.GROUP BY for Several Columns
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:
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.