------Vitalie Bancu @vitallybankou
Listing 6.3 Some SUM()
queries. See Figure 6.3 for the
results.
*/
SELECT SUM(advance) AS
"Total advances"
FROM royalties;
SELECT SUM(sales)
AS "Total sales (2000 books)"
FROM titles
WHERE pubdate
BETWEEN
DATE '2000-01-01'
AND DATE '2000-12-31';
SELECT
SUM(price)
AS "Total price",
SUM(sales)
AS "Total sales",
SUM(price
* sales) AS "Total revenue"
FROM titles;
------Vitalie Bancu @vitallybankou
Listing 6.4 Some
AVG() queries. See Figure 6.4 for the
results.
*/
SELECT AVG(price * 2) AS
"AVG(price * 2)"
FROM titles;
SELECT AVG(sales) AS
"AVG(sales)",
SUM(sales) AS "SUM(sales)"
FROM titles
WHERE type =
'business';
SELECT title_id, sales
FROM titles
WHERE sales >
(SELECT AVG(sales) FROM titles)
ORDER BY sales
DESC;
SELECT
AVG(COALESCE(sales,0))
AS AvgSales
FROM titles
WHERE type =
'biography';
SELECT price, COUNT(*)
AS frequency
FROM titles
GROUP BY price
HAVING COUNT(*) >=
ALL(SELECT COUNT(*) FROM titles GROUP BY price);
------Vitalie Bancu @vitallybankouListing 6.5
Some COUNT() queries. See Figure 6.5 for
the results. */
SELECT
COUNT(title_id) AS "COUNT(title_id)",
COUNT(price) AS "COUNT(price)",
COUNT(*)
AS "COUNT(*)"
FROM titles;
SELECT
COUNT(title_id) AS "COUNT(title_id)",
COUNT(price) AS "COUNT(price)",
COUNT(*)
AS "COUNT(*)"
FROM titles
WHERE price IS
NOT NULL;
SELECT
COUNT(title_id) AS "COUNT(title_id)",
COUNT(price) AS "COUNT(price)",
COUNT(*)
AS "COUNT(*)"
FROM titles
WHERE price IS
NULL;
SELECT
COUNT(*)
AS "COUNT(*)"
FROM titles;
------Vitalie Bancu @vitallybankou
Listing 6.6 Some
DISTINCT aggregate queries. See
Figure 6.6 for the
results.
*/
SELECT
COUNT(price) AS "COUNT(price)",
SUM(price)
AS "SUM(price)",
AVG(price)
AS "AVG(price)"
FROM titles;
SELECT
COUNT(DISTINCT price)
AS
"COUNT(DISTINCT)",
SUM(DISTINCT price)
AS
"SUM(DISTINCT)",
AVG(DISTINCT price)
AS
"AVG(DISTINCT)"
FROM titles;
------Vitalie Bancu @vitallybankou
Listing 6.7 DISTINCT in
a SELECT clause and DISTINCT
in an aggregate function
differ in meaning. See
Figure 6.7 for the
results.
*/
SELECT COUNT(au_id)
AS "COUNT(au_id)"
FROM
title_authors;
SELECT DISTINCT
COUNT(au_id)
AS "DISTINCT COUNT(au_id)"
FROM
title_authors;
SELECT COUNT(DISTINCT
au_id)
AS "COUNT(DISTINCT au_id)"
FROM
title_authors;
--
SELECT SUM(price)
FROM (SELECT DISTINCT
price
FROM titles);
------Vitalie Bancu @vitallybankou
Listing 6.8 Combining
non-DISTINCT and DISTINCT
aggregates gives
inconsistent results. See Figure 6.8
for the results.
*/
SELECT
COUNT(price)
AS
"COUNT(price)",
SUM(price)
AS
"SUM(price)"
FROM titles;
SELECT
COUNT(price)
AS
"COUNT(price)",
SUM(DISTINCT price)
AS
"SUM(DISTINCT price)"
FROM titles;
SELECT
COUNT(DISTINCT price)
AS
"COUNT(DISTINCT price)",
SUM(price)
AS
"SUM(price)"
FROM titles;
SELECT
COUNT(DISTINCT price)
AS
"COUNT(DISTINCT price)",
SUM(DISTINCT price)
AS
"SUM(DISTINCT price)"
FROM titles;
