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

 

Our Random Articles

More Links