SQL/MySQL/Select Clause/Count — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 10:16, 26 мая 2010
Содержание
- 1 Another Count and Group BY
- 2 Count and group
- 3 COUNT() and GROUP BY
- 4 Count and group by two columns
- 5 COUNT command with condition
- 6 Counting Rows: Counting the total number of animals
- 7 COUNT with condition and group
- 8 Performing Row and Column Counting
- 9 Simple COUNT
- 10 Use COUNT and GROUP
- 11 Use COUNT, GROUP and HAVING
- 12 Use COUNT in select command
- 13 Use COUNT with condition
Another Count and Group BY
/*
mysql> /* Number of animals per sex: */
mysql> SELECT sex, COUNT(*) FROM Bird GROUP BY sex;
+------+----------+
| sex | COUNT(*) |
+------+----------+
| f | 1 |
| m | 2 |
+------+----------+
2 rows in set (0.00 sec)
*/
Drop table Bird;
CREATE TABLE Bird (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);
INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
/* Number of animals per sex: */
SELECT sex, COUNT(*) FROM Bird GROUP BY sex;
Count and group
/*
mysql> /* find out how many Birds each owner has: */
mysql> SELECT owner, COUNT(*) FROM Bird GROUP BY owner;
+-------+----------+
| owner | COUNT(*) |
+-------+----------+
| Joe | 1 |
| Yin | 2 |
+-------+----------+
2 rows in set (0.01 sec)
*/
Drop table Bird;
CREATE TABLE Bird (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);
INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
/* find out how many Birds each owner has: */
SELECT owner, COUNT(*) FROM Bird GROUP BY owner;
COUNT() and GROUP BY
/*
mysql> /* COUNT() and GROUP BY
mysql> Number of animals per species:
mysql> */
mysql> SELECT species, COUNT(*) FROM Bird GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| Bus | 2 |
| Car | 1 |
+---------+----------+
2 rows in set (0.00 sec)
*/
Drop table Bird;
CREATE TABLE Bird (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);
INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
/* COUNT() and GROUP BY
Number of animals per species:
*/
SELECT species, COUNT(*) FROM Bird GROUP BY species;
Count and group by two columns
/*
mysql> /* Number of animals per combination of species and sex: */
mysql> SELECT species, sex, COUNT(*) FROM Bird GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| Bus | m | 2 |
| Car | f | 1 |
+---------+------+----------+
2 rows in set (0.02 sec)
*/
Drop table Bird;
CREATE TABLE Bird (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);
INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
/* Number of animals per combination of species and sex: */
SELECT species, sex, COUNT(*) FROM Bird GROUP BY species, sex;
COUNT command with condition
/*
mysql> SELECT species, sex, COUNT(*) FROM Bird
-> WHERE species = "dog" OR species = "cat"
-> GROUP BY species, sex;
Empty set (0.00 sec)
*/
Drop table Bird;
CREATE TABLE Bird (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);
INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
/* You need not retrieve an entire table when you use COUNT().
For example, the previous query, when performed just on dogs and
cats, looks like this:
*/
SELECT species, sex, COUNT(*) FROM Bird
WHERE species = "dog" OR species = "cat"
GROUP BY species, sex;
Counting Rows: Counting the total number of animals
/*mysql> select * from Bird;
+----------+-------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
+----------+-------+---------+------+------------+------------+
| BlueBird | Joe | Car | f | 1999-03-30 | NULL |
| RedBird | Yin | Bus | m | 1979-04-30 | 0000-00-00 |
| RedBird | Yin | Bus | m | 1998-01-30 | NULL |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> SELECT COUNT(*) FROM Bird;
+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+
1 row in set (0.00 sec)
*/
Drop table Bird;
CREATE TABLE Bird (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);
INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
select * from Bird;
SELECT COUNT(*) FROM Bird;
COUNT with condition and group
/*
mysql> SELECT species, sex, COUNT(*) FROM Bird
-> WHERE sex IS NOT NULL
-> GROUP BY species, sex;
+---------+------+----------+
| species | sex | COUNT(*) |
+---------+------+----------+
| Bus | m | 2 |
| Car | f | 1 |
+---------+------+----------+
2 rows in set (0.00 sec)
*/
Drop table Bird;
CREATE TABLE Bird (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);
INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
/* Or, if you wanted the number of animals per sex only for animals whose
sex is known:
*/
SELECT species, sex, COUNT(*) FROM Bird
WHERE sex IS NOT NULL
GROUP BY species, sex;
Performing Row and Column Counting
/*
mysql> SELECT COUNT(*) AS NumberOfExams,
-> COUNT(DISTINCT SustainedOn) AS UniqueDates,
-> COUNT(Comments) AS ExamsWithComments
-> FROM Exam;
+---------------+-------------+-------------------+
| NumberOfExams | UniqueDates | ExamsWithComments |
+---------------+-------------+-------------------+
| 3 | 3 | 3 |
+---------------+-------------+-------------------+
1 row in set (0.00 sec)
*/
/* Create the table */
Drop TABLE Exam;
CREATE TABLE Exam (
ExamID INT NOT NULL PRIMARY KEY,
SustainedOn DATE,
Comments VARCHAR(255)
)TYPE = InnoDB;
/* Insert data */
INSERT INTO Exam (ExamID,SustainedOn,Comments) VALUES (1,"2003-03-12","Java test");
INSERT INTO Exam (ExamID,SustainedOn,Comments) VALUES (2,"2003-03-13","C# test");
INSERT INTO Exam (ExamID,SustainedOn,Comments) VALUES (3,"2003-03-11","JavaScript test");
/* Real command */
SELECT COUNT(*) AS NumberOfExams,
COUNT(DISTINCT SustainedOn) AS UniqueDates,
COUNT(Comments) AS ExamsWithComments
FROM Exam;
Simple COUNT
/*
mysql> select * from employee;
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| id | firstname | lastname | title | age | yearofservice| salary | perks | email |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| 1 | John | Chen | Senior Programmer | 31 | 3| 120000 | 25000 | j@hotmail.ru |
| 2 | Jan | Pillai | Senior Programmer | 32 | 4| 110000 | 20000 | g@yahoo.ru |
| 3 | Ane | Pandit | Web Designer | 24 | 3| 90000 | 15000 | a@gmail.ru |
| 4 | Mary | Anchor | Web Designer | 27 | 2| 85000 | 15000 | m@mail.ru |
| 5 | Fred | King | Programmer | 32 | 3| 75000 | 15000 | f@net.ru |
| 6 | John | Mac | Programmer | 32 | 4| 80000 | 16000 | j@hotmail.ru |
| 7 | Arthur | Sam | Programmer | 28 | 2| 75000 | 14000 | e@yahoo.ru |
| 8 | Alok | Nanda | Programmer | 32 | 3| 70000 | 10000 | a@yahoo.ru |
| 9 | Susan | Ra | Multimedia Programmer | 32 | 4| 90000 | 15000 | h@gmail.ru |
| 10 | Paul | Simon | Multimedia Programmer | 23 | 1| 85000 | 12000 | ps@gmail.ru |
| 11 | Edward | Parhar | Multimedia Programmer | 30 | 2| 75000 | 15000 | a@hotmail.ru |
| 12 | Kim | Hunter | Senior Web Designer | 32 | 4| 110000 | 20000 | kim@coolmail.ru |
| 13 | Roger | Lewis | System Administrator | 32 | 3| 100000 | 13000 | roger@mail.ru |
| 14 | Danny | Gibson | System Administrator | 31 | 2| 90000 | 12000 | danny@hotmail.ru |
| 15 | Mike | Harper | Senior Marketing Executive | 36 | 1| 120000 | 28000 | m@gmail.ru |
| 16 | Mary | Sunday | Marketing Executive | 31 | 5| 90000 | 25000 | monica@bigmail.ru |
| 17 | Jack | Sim | Marketing Executive | 27 | 1| 70000 | 18000 | hal@gmail.ru |
| 18 | Joe | Irvine | Marketing Executive | 27 | 1| 72000 | 18000 | joseph@hotmail.ru |
| 19 | Henry | Ali | Customer Service Manager | 32 | 3| 70000 | 9000 | shahida@hotmail.ru |
| 20 | Peter | Champion | Finance Manager | 32 | 2| 120000 | 25000 | peter@yahoo.ru |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
20 rows in set (0.01 sec)
mysql> select COUNT(*) from employee;
+----------+
| COUNT(*) |
+----------+
| 20 |
+----------+
1 row in set (0.00 sec)
*/
Drop table employee;
CREATE TABLE employee (
id int unsigned not null auto_increment primary key,
firstname varchar(20),
lastname varchar(20),
title varchar(30),
age int,
yearofservice int,
salary int,
perks int,
email varchar(60)
);
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");
select * from employee;
select COUNT(*) from employee;
Use COUNT and GROUP
/*
mysql> select * from Item;
+----+-----------+---------+---------+----------+------------+----------+---------------------+
| ID | Name | InStock | OnOrder | Reserved | Department | Category | RowUpdate |
+----+-----------+---------+---------+----------+------------+----------+---------------------+
| 1 | Bloodshot | 10 | 5 | 1 | Popular | Rock | 2005-10-09 09:19:44 |
| 2 | Most | 10 | 5 | 2 | Classical | Opera | 2005-10-09 09:19:44 |
| 3 | Jazz | 17 | 4 | 3 | Popular | Jazz | 2005-10-09 09:19:44 |
| 4 | Class | 9 | 4 | 4 | Classical | Dance | 2005-10-09 09:19:44 |
| 5 | Violin | 24 | 2 | 5 | Classical | General | 2005-10-09 09:19:44 |
| 6 | Cha Cha | 16 | 6 | 6 | Classical | Vocal | 2005-10-09 09:19:44 |
| 7 | Blues | 2 | 25 | 7 | Popular | Blues | 2005-10-09 09:19:44 |
| 8 | Pure | 32 | 3 | 18 | Popular | Jazz | 2005-10-09 09:19:44 |
| 9 | Mud | 12 | 15 | 19 | Popular | Country | 2005-10-09 09:19:44 |
| 10 | The | 5 | 20 | 11 | Popular | New Age | 2005-10-09 09:19:44 |
| 11 | Embrace | 24 | 11 | 12 | Popular | New Age | 2005-10-09 09:19:44 |
| 12 | Magic | 42 | 17 | 13 | Classical | General | 2005-10-09 09:19:44 |
| 13 | Lake | 25 | 44 | 24 | Classical | Dance | 2005-10-09 09:19:44 |
| 14 | LaLala | 20 | 10 | 5 | Classical | Opera | 2005-10-09 09:19:44 |
| 15 | Soul | 15 | 30 | 16 | Popular | Blues | 2005-10-09 09:19:44 |
| 16 | Stages | 42 | 0 | 7 | Popular | Blues | 2005-10-09 09:19:44 |
| 17 | Six | 16 | 8 | 6 | Classical | General | 2005-10-09 09:19:44 |
+----+-----------+---------+---------+----------+------------+----------+---------------------+
17 rows in set (0.03 sec)
mysql> SELECT Category, COUNT(*) AS Total
-> FROM Item
-> WHERE Department="Popular"
-> GROUP BY Category;
+----------+-------+
| Category | Total |
+----------+-------+
| Blues | 3 |
| Country | 1 |
| Jazz | 2 |
| New Age | 2 |
| Rock | 1 |
+----------+-------+
5 rows in set (0.02 sec)
*/
Drop table Item;
CREATE TABLE Item
(
ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
InStock SMALLINT UNSIGNED NOT NULL,
OnOrder SMALLINT UNSIGNED NOT NULL,
Reserved SMALLINT UNSIGNED NOT NULL,
Department ENUM("Classical", "Popular") NOT NULL,
Category VARCHAR(20) NOT NULL,
RowUpdate TIMESTAMP NOT NULL
);
INSERT INTO Item (Name, InStock, OnOrder, Reserved, Department, Category)
VALUES ("Bloodshot", 10, 5, 1, "Popular", "Rock"),
("Most", 10, 5, 2, "Classical", "Opera"),
("Jazz", 17, 4, 3, "Popular", "Jazz"),
("Class", 9, 4, 4, "Classical", "Dance"),
("Violin", 24, 2, 5, "Classical", "General"),
("Cha Cha", 16, 6, 6, "Classical", "Vocal"),
("Blues", 2, 25, 7, "Popular", "Blues"),
("Pure", 32, 3, 18, "Popular", "Jazz"),
("Mud", 12, 15, 19, "Popular", "Country"),
("The", 5, 20, 11, "Popular", "New Age"),
("Embrace", 24, 11, 12, "Popular", "New Age"),
("Magic", 42, 17, 13, "Classical", "General"),
("Lake", 25, 44, 24, "Classical", "Dance"),
("LaLala", 20, 10, 5, "Classical", "Opera"),
("Soul", 15, 30, 16, "Popular", "Blues"),
("Stages", 42, 0, 7, "Popular", "Blues"),
("Six", 16, 8, 6, "Classical", "General");
select * from Item;
SELECT Category, COUNT(*) AS Total
FROM Item
WHERE Department="Popular"
GROUP BY Category;
Use COUNT, GROUP and HAVING
/*
mysql> select * from Item;
+----+-----------+---------+---------+----------+------------+----------+---------------------+
| ID | Name | InStock | OnOrder | Reserved | Department | Category | RowUpdate |
+----+-----------+---------+---------+----------+------------+----------+---------------------+
| 1 | Bloodshot | 10 | 5 | 1 | Popular | Rock | 2005-10-09 09:19:45 |
| 2 | Most | 10 | 5 | 2 | Classical | Opera | 2005-10-09 09:19:45 |
| 3 | Jazz | 17 | 4 | 3 | Popular | Jazz | 2005-10-09 09:19:45 |
| 4 | Class | 9 | 4 | 4 | Classical | Dance | 2005-10-09 09:19:45 |
| 5 | Violin | 24 | 2 | 5 | Classical | General | 2005-10-09 09:19:45 |
| 6 | Cha Cha | 16 | 6 | 6 | Classical | Vocal | 2005-10-09 09:19:45 |
| 7 | Blues | 2 | 25 | 7 | Popular | Blues | 2005-10-09 09:19:45 |
| 8 | Pure | 32 | 3 | 18 | Popular | Jazz | 2005-10-09 09:19:45 |
| 9 | Mud | 12 | 15 | 19 | Popular | Country | 2005-10-09 09:19:45 |
| 10 | The | 5 | 20 | 11 | Popular | New Age | 2005-10-09 09:19:45 |
| 11 | Embrace | 24 | 11 | 12 | Popular | New Age | 2005-10-09 09:19:45 |
| 12 | Magic | 42 | 17 | 13 | Classical | General | 2005-10-09 09:19:45 |
| 13 | Lake | 25 | 44 | 24 | Classical | Dance | 2005-10-09 09:19:45 |
| 14 | LaLala | 20 | 10 | 5 | Classical | Opera | 2005-10-09 09:19:45 |
| 15 | Soul | 15 | 30 | 16 | Popular | Blues | 2005-10-09 09:19:45 |
| 16 | Stages | 42 | 0 | 7 | Popular | Blues | 2005-10-09 09:19:45 |
| 17 | Six | 16 | 8 | 6 | Classical | General | 2005-10-09 09:19:45 |
+----+-----------+---------+---------+----------+------------+----------+---------------------+
17 rows in set (0.00 sec)
mysql> SELECT Category, COUNT(*) AS Total
-> FROM Item
-> WHERE Department="Popular"
-> GROUP BY Category
-> HAVING Total < 3;
+----------+-------+
| Category | Total |
+----------+-------+
| Country | 1 |
| Jazz | 2 |
| New Age | 2 |
| Rock | 1 |
+----------+-------+
4 rows in set (0.00 sec)
*/
Drop table Item;
CREATE TABLE Item
(
ID SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
InStock SMALLINT UNSIGNED NOT NULL,
OnOrder SMALLINT UNSIGNED NOT NULL,
Reserved SMALLINT UNSIGNED NOT NULL,
Department ENUM("Classical", "Popular") NOT NULL,
Category VARCHAR(20) NOT NULL,
RowUpdate TIMESTAMP NOT NULL
);
INSERT INTO Item (Name, InStock, OnOrder, Reserved, Department, Category)
VALUES ("Bloodshot", 10, 5, 1, "Popular", "Rock"),
("Most", 10, 5, 2, "Classical", "Opera"),
("Jazz", 17, 4, 3, "Popular", "Jazz"),
("Class", 9, 4, 4, "Classical", "Dance"),
("Violin", 24, 2, 5, "Classical", "General"),
("Cha Cha", 16, 6, 6, "Classical", "Vocal"),
("Blues", 2, 25, 7, "Popular", "Blues"),
("Pure", 32, 3, 18, "Popular", "Jazz"),
("Mud", 12, 15, 19, "Popular", "Country"),
("The", 5, 20, 11, "Popular", "New Age"),
("Embrace", 24, 11, 12, "Popular", "New Age"),
("Magic", 42, 17, 13, "Classical", "General"),
("Lake", 25, 44, 24, "Classical", "Dance"),
("LaLala", 20, 10, 5, "Classical", "Opera"),
("Soul", 15, 30, 16, "Popular", "Blues"),
("Stages", 42, 0, 7, "Popular", "Blues"),
("Six", 16, 8, 6, "Classical", "General");
select * from Item;
SELECT Category, COUNT(*) AS Total
FROM Item
WHERE Department="Popular"
GROUP BY Category
HAVING Total < 3;
Use COUNT in select command
Drop table Bird;
CREATE TABLE Bird (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR(1),
birth DATE,
death DATE
);
INSERT INTO Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
/* group together all records for each owner.
Without it, all you get is an error message:
*/
SELECT COUNT(*) FROM Bird;
Use COUNT with condition
/*mysql> select * from employee;
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| id | firstname | lastname | title | age | yearofservice| salary | perks | email |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
| 1 | John | Chen | Senior Programmer | 31 | 3| 120000 | 25000 | j@hotmail.ru |
| 2 | Jan | Pillai | Senior Programmer | 32 | 4| 110000 | 20000 | g@yahoo.ru |
| 3 | Ane | Pandit | Web Designer | 24 | 3| 90000 | 15000 | a@gmail.ru |
| 4 | Mary | Anchor | Web Designer | 27 | 2| 85000 | 15000 | m@mail.ru |
| 5 | Fred | King | Programmer | 32 | 3| 75000 | 15000 | f@net.ru |
| 6 | John | Mac | Programmer | 32 | 4| 80000 | 16000 | j@hotmail.ru |
| 7 | Arthur | Sam | Programmer | 28 | 2| 75000 | 14000 | e@yahoo.ru |
| 8 | Alok | Nanda | Programmer | 32 | 3| 70000 | 10000 | a@yahoo.ru |
| 9 | Susan | Ra | Multimedia Programmer | 32 | 4| 90000 | 15000 | h@gmail.ru |
| 10 | Paul | Simon | Multimedia Programmer | 23 | 1| 85000 | 12000 | ps@gmail.ru |
| 11 | Edward | Parhar | Multimedia Programmer | 30 | 2| 75000 | 15000 | a@hotmail.ru |
| 12 | Kim | Hunter | Senior Web Designer | 32 | 4| 110000 | 20000 | kim@coolmail.ru |
| 13 | Roger | Lewis | System Administrator | 32 | 3| 100000 | 13000 | roger@mail.ru |
| 14 | Danny | Gibson | System Administrator | 31 | 2| 90000 | 12000 | danny@hotmail.ru |
| 15 | Mike | Harper | Senior Marketing Executive | 36 | 1| 120000 | 28000 | m@gmail.ru |
| 16 | Mary | Sunday | Marketing Executive | 31 | 5| 90000 | 25000 | monica@bigmail.ru |
| 17 | Jack | Sim | Marketing Executive | 27 | 1| 70000 | 18000 | hal@gmail.ru |
| 18 | Joe | Irvine | Marketing Executive | 27 | 1| 72000 | 18000 | joseph@hotmail.ru |
| 19 | Henry | Ali | Customer Service Manager | 32 | 3| 70000 | 9000 | shahida@hotmail.ru |
| 20 | Peter | Champion | Finance Manager | 32 | 2| 120000 | 25000 | peter@yahoo.ru |
+----+-----------+----------+----------------------------+------+---------------+--------+-------+---------------------+
20 rows in set (0.01 sec)
mysql> select COUNT(*) from employee
-> where title = "Programmer";
+----------+
| COUNT(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
*/
Drop table employee;
CREATE TABLE employee (
id int unsigned not null auto_increment primary key,
firstname varchar(20),
lastname varchar(20),
title varchar(30),
age int,
yearofservice int,
salary int,
perks int,
email varchar(60)
);
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Chen", "Senior Programmer", 31, 3, 120000, 25000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jan", "Pillai", "Senior Programmer", 32, 4, 110000, 20000, "g@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Ane", "Pandit", "Web Designer", 24, 3, 90000, 15000, "a@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Anchor", "Web Designer", 27, 2, 85000, 15000, "m@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Fred", "King", "Programmer", 32, 3, 75000, 15000, "f@net.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("John", "Mac", "Programmer", 32, 4, 80000, 16000, "j@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Arthur", "Sam", "Programmer", 28, 2, 75000, 14000, "e@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Alok", "Nanda", "Programmer", 32, 3, 70000, 10000, "a@yahoo.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Susan", "Ra", "Multimedia Programmer", 32, 4, 90000, 15000, "h@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Paul", "Simon", "Multimedia Programmer", 23, 1, 85000, 12000, "ps@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Edward", "Parhar", "Multimedia Programmer", 30, 2, 75000, 15000, "a@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Kim", "Hunter", "Senior Web Designer", 32, 4, 110000, 20000, "kim@coolmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Roger", "Lewis", "System Administrator", 32, 3, 100000, 13000, "roger@mail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Danny", "Gibson", "System Administrator", 31, 2, 90000, 12000, "danny@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mike", "Harper", "Senior Marketing Executive", 36, 1, 120000, 28000, "m@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Mary", "Sunday", "Marketing Executive", 31, 5, 90000, 25000, "monica@bigmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Jack", "Sim", "Marketing Executive", 27, 1, 70000, 18000, "hal@gmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Joe", "Irvine", "Marketing Executive", 27, 1, 72000, 18000, "joseph@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Henry", "Ali", "Customer Service Manager", 32, 3, 70000, 9000, "shahida@hotmail.ru");
INSERT INTO employee (firstname, lastName, title, age, yearofservice, salary, perks, email) values ("Peter", "Champion", "Finance Manager", 32, 2, 120000, 25000, "peter@yahoo.ru");
select * from employee;
select COUNT(*) from employee
where title = "Programmer";