Example: Using SELECT COUNT(*) in a Correlated Subquery - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

Select the names of the publishers whose book count values in the library match the actual count of books.

     SELECT pubname, bookcount
     FROM library
     WHERE (bookcount, pubnum) IN (SELECT COUNT(*), book.pubnum
                                   FROM book
                                   GROUP BY pubnum);

If the book count for a publisher in the library is zero, then the name of that publisher is not returned because no row is returned by the subquery for this publisher.

Note that the result data type for a COUNT operation is different for ANSI and Teradata session modes, as described by the following table. For more information, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

IN this session mode … THE data type of the result for a COUNT operation is …
ANSI DECIMAL(p,0)

where p represents the precision of the number.

  • If the DBS Control field MaxDecimal is set to any of the following values, then the value of p is 15.

      0

    15

    18

  • If the DBS Control field MaxDecimal is set to 31, then the value of p is also 31.
  • If the DBS Control field MaxDecimal is set to 38, then the value of p is also 38.
Teradata INTEGER

Another equivalent SELECT statement uses two noncorrelated subqueries to return the same answer set as follows:

     SELECT pub_name, book_count
     FROM library
     WHERE (book_count, pub_num) IN (SELECT COUNT(*), pub_num
                                     FROM book
                                     GROUP BY pub_num)
                          OR NOT IN (SELECT book.pub_num
                                     FROM book
                                     GROUP BY pub_num)
                                 AND book_count = 0;

The following SELECT statement, which is less complicated and more elegant, uses a correlated subquery to return the same correct answer as the previous query.

     SELECT pub_name, book_count
     FROM library
     WHERE book_count IN (SELECT count(*)
                          FROM book
                          WHERE book.pub_num = library.pub_num);