|
ALL keyword after GROUP BY uses all row values in the grouping, even if they were not qualified to appear via the WHERE clause.
Null value is eliminated by an aggregate or other SET operation.
7>
8> CREATE TABLE employee(
9> id INTEGER NOT NULL PRIMARY KEY,
10> first_name VARCHAR(10),
11> last_name VARCHAR(10),
12> salary DECIMAL(10,2),
13> start_Date DATETIME,
14> region VARCHAR(10),
15> city VARCHAR(20)
16> );
17> GO
1> INSERT INTO employee VALUES (1, 'Jason' , 'Martin', 5890,'2005-03-22','North','Vancouver');
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, 'Alison', 'Mathews',4789,'2003-07-21','South','Utown');
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, 'James' , 'Smith', 6678,'2001-12-01','North','Paris');
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, 'Celia' , 'Rice', 5567,'2006-03-03','South','London');
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, 'Robert', 'Black', 4467,'2004-07-02','East','Newton');
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, 'Linda' , 'Green' , 6456,'2002-05-19','East','Calgary');
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, 'David' , 'Larry', 5345,'2008-03-18','West','New York');
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, 'James' , 'Cat', 4234,'2007-07-17','West','Regina');
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, 'Joan' , 'Act', 6123,'2001-04-16','North','Toronto');
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city
----------- ---------- ---------- ------------ ----------------------- ---------- --------------------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto
(9 rows affected)
1>
2>
3>
4> SELECT start_Date,
5> SUM(salary) Total
6> FROM employee
7> WHERE start_Date BETWEEN '7/1/2001' AND '7/31/2005'
8> GROUP BY ALL Region;
9> GO
Msg 8120, Level 16, State 1, Server BCE67B1242DE45A\SQLEXPRESS, Line 4
Column 'employee.start_Date' is invalid in the select list because it is not contained in either an aggregate function o
r the GROUP BY clause.
1>
2>
3>
4> drop table employee;
5> GO
1>
|