Database Reference
In-Depth Information
SELECT COUNT(e.*)
FROM (SELECT product_id
FROM orders_arch
EXCEPT
SELECT product_id
FROM orders_recent) e
13569
The preceding query uses the
COUNT
aggregate function to determine the number
of returned rows from a second SQL query that includes the
EXCEPT
operator. This
SQL query within a query is sometimes called a
subquery
or a
nested query
.
Subqueries enable the construction of fairly complex queries without having to first
execute the pieces, dump the rows to temporary tables, and then execute another
SQL query to process those temporary tables. Subqueries can be used in place of a
table within the
FROM
clause or can be used in the
WHERE
clause.
11.1.3 Grouping Extensions
Previously, the
COUNT()
aggregate function was used to count the number of
returned rows from a query. Such aggregate functions often summarize a dataset
after applying some grouping operation to it. For example, it may be desired to
know the revenue by year or shipments per week. The following SQL query uses
the
SUM()
aggregate function along with the
GROUP BY
operator to provide the
top three ordered items based on
item_quantity
.
SELECT i.product_id,
SUM(i.item_quantity) AS total
FROM orders_recent i
GROUP BY i.product_id
ORDER BY SUM(i.item_quantity) DESC
LIMIT 3
product_id total
15072 6089
15066 6082
15060 6053
GROUP BY
can use the
ROLLUP()
operator to calculate subtotals and grand totals.
The following SQL query employs the previous query as a subquery in the
WHERE
