Mega Code Archive

LEFT JOIN with group by and order by

1> 2> create table employee( 3> ID int, 4> name nvarchar (10), 5> salary int ) 6> GO 1> 2> create table job( 3> ID int, 4> title nvarchar (10), 5> averageSalary int) 6> GO 1> 2> 3> insert into employee (ID, name, salary) values (1, 'Jason', 1234) 4> GO 1> insert into employee (ID, name, salary) values (2, 'Robert', 4321) 2> GO 1> insert into employee (ID, name, salary) values (3, 'Celia', 5432) 2> GO 1> insert into employee (ID, name, salary) values (4, 'Linda', 3456) 2> GO 1> insert into employee (ID, name, salary) values (5, 'David', 7654) 2> GO 1> insert into employee (ID, name, salary) values (6, 'James', 4567) 2> GO 1> insert into employee (ID, name, salary) values (7, 'Alison', 8744) 2> GO 1> insert into employee (ID, name, salary) values (8, 'Chris', 9875) 2> GO 1> insert into employee (ID, name, salary) values (9, 'Mary', 2345) 2> GO 1> 2> insert into job(ID, title, averageSalary) values(1,'Developer',3000) 3> GO 1> insert into job(ID, title, averageSalary) values(2,'Tester', 4000) 2> GO 1> insert into job(ID, title, averageSalary) values(3,'Designer', 5000) 2> GO 1> insert into job(ID, title, averageSalary) values(4,'Programmer', 6000) 2> GO 1> 2> 3> select * from employee; 4> GO ID name salary ----------- ---------- ----------- 1 Jason 1234 2 Robert 4321 3 Celia 5432 4 Linda 3456 5 David 7654 6 James 4567 7 Alison 8744 8 Chris 9875 9 Mary 2345 1> select * from job; 2> GO ID title averageSalary ----------- ---------- ------------- 1 Developer 3000 2 Tester 4000 3 Designer 5000 4 Programmer 6000 1> 2> SELECT count(e.id) 3> FROM Employee e LEFT JOIN job j 4> ON e.ID = j.ID 5> group by j.ID 6> ORDER BY j.ID 7> GO ----------- 5 1 1 1 1 1> 2> 3> 4> drop table employee; 5> drop table job; 6> GO 1> 2>