Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.
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.