------Vitalie Bancu @vitallybankou

Listing 6.9 List the number of books each author

wrote (or cowrote). See Figure 6.9 for the result.

*/  

  SELECT

    au_id,

    COUNT(*) AS "num_books"

  FROM title_authors

  GROUP BY au_id

  ORDER BY AU_ID ASC;

  ------Vitalie Bancu @vitallybankou

Listing 6.10 This query illustrates the difference

between COUNT(expr) and COUNT(*) in a GROUP BY

query. See Figure 6.10 for the result.  

*/

SELECT

    state,

    COUNT(state) AS "COUNT(state)",

    COUNT(*)     AS "COUNT(*)"

  FROM publishers

  GROUP BY state;

    ------Vitalie Bancu @vitallybankou

  Listing 6.11 For mathematically consistent results,

use COUNT(expr), rather than COUNT(*), if expr

contains nulls. See Figure 6.11 for the result.

  */

SELECT

    type,

    SUM(sales)   AS "SUM(sales)",

    COUNT(sales) AS "COUNT(sales)",

    COUNT(*)     AS "COUNT(*)",

    SUM(sales)/COUNT(sales)

      AS "SUM/COUNT(sales)",

    SUM(sales)/COUNT(*)

      AS "SUM/COUNT(*)",

    AVG(sales)   AS "AVG(sales)"

  FROM titles

  GROUP BY type;

  ------Vitalie Bancu @vitallybankouListing 6.12 This simple GROUP BY query calculates a

few summary statistics for each type of book. See

Figure 6.12 for the result.*/

SELECT

    type,

    SUM(sales)   AS "SUM(sales)",

    AVG(sales)   AS "AVG(sales)",

    COUNT(sales) AS "COUNT(sales)"

  FROM titles

  GROUP BY type;

  ------Vitalie Bancu @vitallybankou

Listing 6.13 Here, I’ve added WHERE and ORDER BY

clauses to Listing 6.12 to cull books priced less than

$13 and sort the result by descending total sales. See

Figure 6.13 for the result.

*/  

SELECT

    type,

    SUM(sales)   AS "SUM(sales)",

    AVG(sales)   AS "AVG(sales)",

    COUNT(sales) AS "COUNT(sales)"

  FROM titles

  WHERE price >= 13

  GROUP BY type

  ORDER BY "SUM(sales)" DESC;

  ------Vitalie Bancu @vitallybankou  

Listing 6.14 List the number of books of each type for

each publisher, sorted by descending count within

ascending publisher ID. See Figure 6.14 for the result.  

*/

SELECT

    pub_id,

    type,

    COUNT(*) AS "COUNT(*)"

  FROM titles

  GROUP BY pub_id, type

  ORDER BY pub_id ASC, "COUNT(*)" DESC;

  ------Vitalie Bancu @vitallybankou

Listing 6.15 List the number of books in each

calculated sales range, sorted by ascending sales.

See Figure 6.15 for the result.

*/  

SELECT

    CASE

      WHEN sales IS NULL

        THEN 'Unknown'

      WHEN sales <= 1000

        THEN 'Not more than 1,000'

      WHEN sales <= 10000 

        THEN 'Between 1,001 and 10,000'

      WHEN sales <= 100000

        THEN 'Between 10,001 and 100,000'

      WHEN sales <= 1000000

        THEN 'Between 100,001 and 1,000,000'

      ELSE 'Over 1,000,000'

    END

      AS "Sales category",

    COUNT(*) AS "Num titles"

  FROM titles

  GROUP BY

    CASE

      WHEN sales IS NULL

        THEN 'Unknown'

      WHEN sales <= 1000

        THEN 'Not more than 1,000'

      WHEN sales <= 10000 

        THEN 'Between 1,001 and 10,000'

      WHEN sales <= 100000

        THEN 'Between 10,001 and 100,000'

      WHEN sales <= 1000000

        THEN 'Between 100,001 and 1,000,000'

      ELSE 'Over 1,000,000'

    END

  ORDER BY MIN(sales) ASC;

  ------Vitalie Bancu @vitallybankou

Listing 6.16 Both of these queries return the same

result. The bottom form is preferred. See Figure 6.16

for the result.

*/  

  SELECT type

  FROM titles

  GROUP BY type;

SELECT DISTINCT type

  FROM titles;

  ------Vitalie Bancu @vitallybankou

Listing 6.17 List the average sales for each price,

sorted by ascending price. See Figure 6.17 for the

result.

*/  

  SELECT price, AVG(sales) AS "AVG(sales)"

  FROM titles

  WHERE price IS NOT NULL

  GROUP BY price

  ORDER BY price ASC;