------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;