Teradata GROUP BY

Teradata GROUP BY is used to make the group of related records from the result set. The group by clause identifies the records which are having the same value (i.e., duplicate value) for the specified attribute and returns a single row of information instead of all the rows where the attribute has the same value.

This can be done by specifying one or more columns in a table as grouping column(s).

Teradata GROUP BY syntax

Following is the generic syntax of GROUP BY.

SELECT column_name 
FROM DatabaseName.TableName
GROUP BY column_name;

If you are using the aggregate function, you can use the following syntax.

SELECT column1 , sum(column2)
FROM DatabaseName.TableName
GROUP BY column1;

Please note that the column name can be used with the aggregate function only if it appears in the GROUP BY clause.

Teradata GROUP BY example

Consider the following Employee table.

SELECT * FROM TUTORIALSBOOK.Employee_bak
ORDER BY 1;

 *** Query completed. 8 rows found. 7 columns returned.
 *** Total elapsed time was 1 second.

      EMPNO  FIRST_NAME                 LAST_NAME                    HIREDATE  DESIGNATION               SALARY       DEPTNO
-----------  -------------------------  -------------------------  ----------  --------------------  ----------  -----------
       1001  STEFAN                     SALVATORE                  2001-04-12  BUSINESS ANALYST        55000.00           40
       1002  DIANA                      LORANCE                    2005-01-05  TECHNICAL ARCHITECT     75000.00           10
       1003  JAMES                      MADISON                    2005-09-18  MANAGER                 60000.00           20
       1004  JONES                      NICK                       2004-08-19  HR ANALYST              35000.00           30
       1005  LUCY                       GILLER                     2006-04-01  HR ASSOCIATE            30000.00           30
       1006  ISSAC                      STEFAN                     2002-11-18  TRAINEE                 22000.00           10
       1007  NANCY                      GILBERT                    2003-04-06  CLERK                   18000.00           10
       1008  JOHN                       SMITH                      2005-10-12  SALESMAN                20000.00           10

The below statement finds the numbers of employees in the different departments.

SELECT DEPTNO,COUNT(EMPNO)
FROM TUTORIALSBOOK.Employee
GROUP BY DEPTNO;

The GROUP BY clause also supports column position same as the ORDER BY clause.

SELECT DEPTNO,COUNT(EMPNO)
FROM TUTORIALSBOOK.Employee
GROUP BY 1;

Output:

SELECT DEPTNO,COUNT(EMPNO)
FROM TUTORIALSBOOK.Employee
GROUP BY DEPTNO;

 *** Query completed. 4 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

     DEPTNO  Count(EMPNO)
-----------  ------------
         40             1
         30             2
         20             1
         10             4

ORDER BY clause can be also used with GROUP BY to sort the final result set.

SELECT DEPTNO,COUNT(EMPNO)
FROM TUTORIALSBOOK.Employee
GROUP BY 1
ORDER BY 1;

Output:

SELECT DEPTNO,COUNT(EMPNO)
FROM TUTORIALSBOOK.Employee
GROUP BY 1
ORDER BY 1;

 *** Query completed. 4 rows found. 2 columns returned.
 *** Total elapsed time was 1 second.

     DEPTNO  Count(EMPNO)
-----------  ------------
         10             4
         20             1
         30             2
         40             1