17.05 - Example: Using a HAVING Clause to Aggregate a Join - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

The columns named price and sales_qty are from two different tables, sales_hist as table_1 and unit_price_cost as table_2. Use the following SELECT statement to find which category of items is sold for a profit margin greater than $1000.

     SELECT table_1.category,
     (table_2.price - table_2.cost) * SUM (table_1.sales_qty) AS margin
     FROM sales_hist AS table_1, unit_price_cost AS table_2
     WHERE table_1.prod_no=table_2.prodno
     GROUP BY table_1.category, table_2.price, table_2.cost
     HAVING margin > 1000;

A subquery can have a join on a view with an aggregate operation and a HAVING clause that references more than one table.