GROUP BY builds different groups for all possible values (NULL, too!)


1>
2> CREATE TABLE works_on        (emp_no       INTEGER NOT NULL,
3>                         project_no    CHAR(4) NOT NULL,
4>                         job CHAR (15) NULL,
5>                         enter_date    DATETIME NULL)
6>
7> insert into works_on values (1, 'p1', 'analyst', '1997.10.1')
8> insert into works_on values (1, 'p3', 'manager', '1999.1.1')
9> insert into works_on values (2, 'p2', 'clerk',   '1998.2.15')
10> insert into works_on values (2, 'p2',  NULL,     '1998.6.1')
11> insert into works_on values (3, 'p2',  NULL,     '1997.12.15')
12> insert into works_on values (4, 'p3', 'analyst', '1998.10.15')
13> insert into works_on values (5, 'p1', 'manager', '1998.4.15')
14> insert into works_on values (6, 'p1',  NULL,     '1998.8.1')
15> insert into works_on values (7, 'p2', 'clerk',   '1999.2.1')
16> insert into works_on values (8, 'p3', 'clerk',   '1997.11.15')
17> insert into works_on values (7, 'p1', 'clerk',   '1998.1.4')
18> 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>
2> -- GROUP BY builds different groups for all possible values (NULL, too!)
3>
4> SELECT project_no, job FROM works_on GROUP BY project_no, job
5> GO
project_no job
---------- ---------------
p1         NULL
p1         analyst
p1         clerk
p1         manager
p2         NULL
p2         clerk
p3         analyst
p3         clerk
p3         manager

(9 rows affected)
1>
2> drop table works_on
3> GO
1>

Related examples in the same category

1.	GROUP BY clause with an aggregator 'SUM()'
2.	Combine MIN function with group by
3.	Combine MAX function with group by
4.	When grouping on more than one column, every unique combination of grouped values produces a row in the result set
5.	Count() and Sum() with group by and column alias
6.	Subquery as one column
7.	Simple 'group by' demo
8.	Count with 'group by'

GROUP BY builds different groups for all possible values (NULL, too!) : Group BY « Select Query « SQL Server / T-SQL

Related examples in the same category