Admin: 1 версия

2010-05-26T10:16:47Z

1 версия

Новая страница

==Another Count and Group BY==



<source lang="sql">
/*
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;

</source>

==Count and group==



<source lang="sql">
/*
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;

</source>

==COUNT() and GROUP BY==



<source lang="sql">
/*
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;

</source>

==Count and group by two columns==



<source lang="sql">
/*
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;

</source>

==COUNT command with condition==



<source lang="sql">
/*
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;

</source>

==Counting Rows: Counting the total number of animals==



<source lang="sql">
/*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;

</source>

==COUNT with condition and group==



<source lang="sql">
/*
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;

</source>

==Performing Row and Column Counting==



<source lang="sql">
/*
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;

</source>

==Simple COUNT==



<source lang="sql">
/*
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;

</source>

==Use COUNT and GROUP==



<source lang="sql">
/*
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;

</source>

==Use COUNT, GROUP and HAVING==



<source lang="sql">
/*
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;

</source>

==Use COUNT in select command==



<source lang="sql">

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;

</source>

==Use COUNT with condition==



<source lang="sql">
/*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";

</source>

SQL/MySQL/Select Clause/Count - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)