Hint: Their teams ended up taking quizzes, too.
create table employee (Name varchar(50), avg1 numeric(5),dob datetime, salary numeric(10,2),location varchar(1),bonus numeric(10,2)); insert into employee values ('AAA',31,'02/19/1980',25000,'A',45000); insert into employee values ('AA1',44,'08/08/1982',35000,'A',35000); insert into employee values ('AA3',10,'10/10/1975',25000,'B',50000); insert into employee values ('AA4',30,'12/10/1999',20000,'B',25000);
SELECT seq, CASE WHEN location IS NULL THEN 'Grand Total' WHEN name IS NULL THEN 'Total' ELSE name END name, AVG, dob, salary, location, bonus FROM (SELECT row_number() over(partition by location order by name) seq, t.* FROM employee t UNION ALL SELECT NULL, NULL, SUM(AVG) AVG, NULL, SUM(salary) salary, location, SUM(bonus) bonus FROM employee GROUP BY ROLLUP(location)) x ORDER BY CASE WHEN location IS NULL THEN 1 ELSE 0 END, location, CASE WHEN x.name IS NULL THEN 1 ELSE 0 END, x.name;
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
Join the community of 500,000 technology professionals and ask your questions.