/* Copy and paste from here up to end into your Query Analyzer 
command window. */

/* These test codes show how to use the compute by command. */

/* If you mess it up, just go back to the top and start again. 
First, we drop the table we created. Don't worry if there is 
error message. Highlight and execute the following drop command. */

drop table TestEmployee

/* Let's create a test table containing data about our employees. 
Highlight and execute the following cretae table command. */

create table TestEmployee ( iEmpNo int identity (100,1), CName char (20), 
                            cDeptCode char (2), cEmptype char (1), mSalary money )

/* Now, let's populate our table. Highlight and execute all the 
following insert commands at the same time. */

insert TestEmployee values ( 'Rowel Ruiz', '01', 'P', 5000 )
insert TestEmployee values ( 'Wilmer Tan', '02', 'C', 6000 )
insert TestEmployee values ( 'Mika Escala', '01', 'C', 7000 )
insert TestEmployee values ( 'Wilhelm Lim', '02', 'P', 8000 )
insert TestEmployee values ( 'Sonnie Go', '03', 'P', 7000 )
insert TestEmployee values ( 'Tina Dy', '03', 'C', 9000 )
insert TestEmployee values ( 'Cesar Aganon', '01', 'C', 9000 )
insert TestEmployee values ( 'Grace Aganon', '02', 'P', 6000 )

/* Now, let's see if our table was properly populated. Highlight 
and execute the next command. */

select * from TestEmployee

/* So, our table is complete. Now.let's manipulate the data. Let's 
try displaying the rows by Department Code. Highlight and execute 
the next select command. */

select * from TestEmployee order by cDeptCode

/* Did you notice how the rows were grouped together based on 
the employee's department code? Now, let's show the total 
salaries of all our employees. Highlight and execute the next 
select command. */

select * from TestEmployee order by cDeptCode compute sum ( mSalary )

/* Did you see the total? Now, let's show our employees' total 
salaries by department code. Highlight and execute the next select command. */

select * from TestEmployee order by cDeptCode compute sum ( mSalary ) by cDeptCode

/* Did you see the departmental totals? Okay, now let's show both the departmental 
totals and the overall total in just one command. Highlight and execute the 
following select command. */

select * from TestEmployee order by cDeptCode compute sum ( mSalary ) by cDeptCode 
                                              compute sum ( mSalary )

/* Did you see the totals by department as well as the overall total? Simple, isn't it?
Now, what I want you to do is to repeat what I have just done but now using the 
employee type code, C is for Contractual and P is for Permanent. First, show the 
overall salary total; then show the totals by employe type; and the show both the totals 
by employee type and the overall total. I have already dione it below but I want 
you to try it first on your own. Okay, start from here.... */


/* Below is the solution. First, we display all employees by employment type. 
Highlight and execute the next select command. */ 

select * from TestEmployee order by cEmpType

/* Then, we show the grand total of the employees' salaries. Highlight and 
execute the next command. */ 

select * from TestEmployee order by cEmpType compute sum ( mSalary )

/* Next, we show the total salaries by employment type. Highlight and 
execute the next command. */

select * from TestEmployee order by cEmpType compute sum ( mSalary ) by cEmpType

/* Now, we show both the totals by employment type as well as the grand total. 
Highlight and execute the next command. */

select * from TestEmployee order by cEmpType compute sum ( mSalary ) by cEmpType
                                             compute sum ( mSalary )

/* That's it. Now, you know how to use the compute by and the compute command. 
Now, you can create your own example by creating your own test tables and 
manipulating your data like how we did above. Happy Coding!