Example: Simple Subquery Using an AVG Aggregate Expression - Teradata Database - Teradata Vantage NewSQL Engine

SQL Data Manipulation Language

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
fbo1512081269404.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

The following SELECT statement finds every employee in the employee table with a salary that is greater than the average salary of all employees in the table.

     SELECT name, dept_no, jobtitle, salary
     FROM employee
     WHERE salary > (SELECT AVG(salary)
                     FROM employee)
     ORDER BY name;

The statement returns this results table.

name

----

dept_no

-------

jobtitle

--------

salary

------

Aguilar J 600 Manager 45,000.00
Carter J 500 Engineer 44,000.00
Omura H 500 Programmer 40,000.00
Phan A 300 Vice President 55,000.00
Regan R 600 Purchaser 44,000.00
Russell S 300 President 65,000.00
Smith T 500 Engineer 42,000.00
Smith T 700 Manager 45,000.00
Watson L 500 Vice President 56,000.00