SQL: HAVING clause

 

‘HAVING’ clause is used to filter the grouped data.

 

CREATE TABLE person (

    id int AUTO_INCREMENT,

    name varchar(20),

    city varchar(25),

    country varchar(25),

    PRIMARY KEY(id)

);

 

Insert following data to person table.

 

INSERT INTO person(id, name, city, country) VALUES (1, 'Krishna', 'Bangalore', 'India');

INSERT INTO person(id, name, city, country) VALUES (2, 'Ram', 'Delhi', 'India');

INSERT INTO person(id, name, city, country) VALUES (3, 'Chamu', 'Berlin', 'Germany');

INSERT INTO person(id, name, city, country) VALUES (4, 'Rama Devi', 'London', 'England');

INSERT INTO person(id, name, city, country) VALUES (5, 'Lakshman', 'Bangalore', 'India');

 

INSERT INTO person(id, name, city, country) VALUES (6, 'Balu', 'Bangalore', 'India');

INSERT INTO person(id, name, city, country) VALUES (7, 'Ravindra', 'Delhi', 'India');

INSERT INTO person(id, name, city, country) VALUES (8, 'Srinu', 'Berlin', 'Germany');

INSERT INTO person(id, name, city, country) VALUES (9, 'Sailu', 'London', 'England');

INSERT INTO person(id, name, city, country) VALUES (10, 'Venkat', 'Bangalore', 'India');

 

INSERT INTO person(id, name, city, country) VALUES (11, 'Balu', 'London', 'England');

INSERT INTO person(id, name, city, country) VALUES (12, 'Ravindra', 'Delhi', 'India');

INSERT INTO person(id, name, city, country) VALUES (13, 'Srinu', 'Berlin', 'Germany');

INSERT INTO person(id, name, city, country) VALUES (14, 'Sailu', 'London', 'England');

INSERT INTO person(id, name, city, country) VALUES (15, 'Venkat', 'Bangalore', 'India');

 

 

Get all the data from person table. 

mysql> SELECT * FROM person;
+----+-----------+-----------+---------+
| id | name      | city      | country |
+----+-----------+-----------+---------+
|  1 | Krishna   | Bangalore | India   |
|  2 | Ram       | Delhi     | India   |
|  3 | Chamu     | Berlin    | Germany |
|  4 | Rama Devi | London    | England |
|  5 | Lakshman  | Bangalore | India   |
|  6 | Balu      | Bangalore | India   |
|  7 | Ravindra  | Delhi     | India   |
|  8 | Srinu     | Berlin    | Germany |
|  9 | Sailu     | London    | England |
| 10 | Venkat    | Bangalore | India   |
| 11 | Balu      | London    | England |
| 12 | Ravindra  | Delhi     | India   |
| 13 | Srinu     | Berlin    | Germany |
| 14 | Sailu     | London    | England |
| 15 | Venkat    | Bangalore | India   |
+----+-----------+-----------+---------+
15 rows in set (0.00 sec)

Count all the persons by country

SELECT country, count(id) FROM person GROUP BY country;

mysql> SELECT country, count(id) FROM person GROUP BY country;
+---------+-----------+
| country | count(id) |
+---------+-----------+
| India   |         8 |
| Germany |         3 |
| England |         4 |
+---------+-----------+
3 rows in set (0.00 sec)

Count all the persons by country where number of person are > 3.

SELECT country, count(id) FROM person GROUP BY country HAVING count(id) > 3;

mysql> SELECT country, count(id) FROM person GROUP BY country HAVING count(id) > 3;
+---------+-----------+
| country | count(id) |
+---------+-----------+
| India   |         8 |
| England |         4 |
+---------+-----------+
2 rows in set (0.01 sec)

 

 

Previous                                                    Next                                                    Home