HAVING with the aggregate function COUNT(*)
1> CREATE TABLE works_on (emp_no INTEGER NOT NULL,
2> project_no CHAR(4) NOT NULL,
3> job CHAR (15) NULL,
4> enter_date DATETIME NULL)
5> GO
1> insert into works_on values (1, 'p1', 'analyst', '1997.10.1')
2> insert into works_on values (1, 'p3', 'manager', '1999.1.1')
3> insert into works_on values (2, 'p2', 'clerk', '1998.2.15')
4> insert into works_on values (2, 'p2', NULL, '1998.6.1')
5> insert into works_on values (3, 'p2', NULL, '1997.12.15')
6> insert into works_on values (4, 'p3', 'analyst', '1998.10.15')
7> insert into works_on values (5, 'p1', 'manager', '1998.4.15')
8> insert into works_on values (6, 'p1', NULL, '1998.8.1')
9> insert into works_on values (7, 'p2', 'clerk', '1999.2.1')
10> insert into works_on values (8, 'p3', 'clerk', '1997.11.15')
11> insert into works_on values (7, 'p1', 'clerk', '1998.1.4')
12> -- HAVING with the aggregate function COUNT(*).
13> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> SELECT project_no FROM works_on GROUP BY project_no HAVING COUNT(*) < 4
2> GO
project_no
----------
p3
(1 rows affected)
1> drop table works_on
2> GO
1>
2>
Related examples in the same category