ReadVitamin

In Teradata not only we can display the totals and the sub totals but also we can show the details of the row(s) that goes into creating these totals. This can be achieved with the use of WITH and WITH…BY functions.Let’s understand this with the following example dataset.

Table Name – EMPLOYEE

emp_no	dept_no	first_name	last_name	salary
7000	114	Read	Vitamin	62,000
4000	113	John	Row	55,000
5000	114	Robert	Howard	65,000
2000	112	Larry	King	60,000
1000	112	Philip	Josheph	65,000
6000	115	Tony	Peter	72,000
3000	111	Micheal	Reed	46,000

Create the volatile (temporary) table “employee” in Teradata with the following create table command;

CREATE SET VOLATILE TABLE <username>.employee, NO FALLBACK,

CHECKSUM = DEFAULT,

LOG

(

“emp_no” INTEGER,

“dept_no” VARCHAR (10),

“first_name” CHAR (20) CHARACTER SET LATIN NOT CASESPECIFIC,

“last_name” CHAR (20) CHARACTER SET LATIN NOT CASESPECIFIC,

“salary” INTEGER

)PRIMARY INDEX ( “emp_no” )

ON COMMIT PRESERVE ROWS;

Insert values into this temp table using the following insert commands.

INSERT INTO employee VALUES (1000,112,’Philip’,'Josheph’,65000);
INSERT INTO employee VALUES (2000,112,’Larry’,'King’,60000);
INSERT INTO employee VALUES (3000,111,’Micheal’,'Reed’,46000);
INSERT INTO employee VALUES (4000,113,’John’,'Row’,55000);
INSERT INTO employee VALUES (5000,114,’Robert’,'Howard’,65000);
INSERT INTO employee VALUES (6000,115,’Tony’,'Peter’,72000);
INSERT INTO employee VALUES (7000,114,’Read’,'Vitamin’,62000);

To display just the totals with a customized title to the total figure use the following command;

SELECT first_name, last_name, dept_no, salary
FROM employee
WITH SUM (salary) (TITLE ‘Total Salary :’);

If you are using Teradata SQL Assistant 7.1 then there is a chance that you might NOT be seeing the row with the total amount. Use BTEQ instead to see the results, but before that make sure you create volatile table and insert data into it.

Following result set would get displayed;

first_name            last_name             dept_n       salary
——————– ——————– ———- ———–
Read                    Vitamin                114              62000
John                     Row                     113              55000
Robert                  Howard                114              65000
Larry                    King                     112              60000
Philip                   Josheph                112              65000
Tony                    Peter                    115              72000
Micheal                Reed                    111              46000
                                                                     ———–
                                        Total Salary:             425000

These results can further be sub grouped/divided by department they work for.

For example, if you would like to see the sub totals based on the dept_no then run the following command;

SELECT first_name,
             last_name,
             dept_no,
             salary
FROM employee
WITH SUM(salary) (TITLE ‘Department SubTotal:’) BY dept_no
WITH SUM (salary) (TITLE ‘Total Salary:’);

You should be seeing the following resultset.

first_name            last_name             dept_no       salary
——————– ——————– ———-       ———–
Micheal                   Reed                   111              46000
                                                                         ———–
                                  Department SubTotal:         46000
Larry                 King                         112              60000
Philip                Josheph                    112              65000
                                                                        ———–
                                  Department SubTotal:       125000
John                  Row                         113              55000
                                                                        ———–
                                  Department SubTotal:         55000
Read                  Vitamin                   114              62000
Robert                Howard                   114              65000
                                                                         ———–
                                  Department SubTotal:       127000
Tony                  Peter                        115             72000
                                                                        ———–
                                  Department SubTotal:         72000
                                                                        ———–
                                         Total Salary:              425000