aggregate functions

min/max

  SELECT
        MIN(price) AS "Min price",
        MAX(price) AS "Max price",
        MAX(price) - MIN(price) AS "Range"
     FROM titles;

sum

  SELECT SUM(sales)
           AS "Total sales (2000 books)"
    FROM titles
    WHERE pubdate
      BETWEEN DATE '2000-01-01'
          AND DATE '2000-12-31';

avg

  SELECT AVG(sales) AS "AVG(sales)",
         SUM(sales) AS "SUM(sales)"
    FROM titles
    WHERE type = 'business';

count

    SELECT title_id, sales
      FROM titles
      WHERE sales >
            (SELECT AVG(sales) FROM titles)
      ORDER BY sales DESC;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;

    SELECT
        COUNT(price) AS "COUNT(price)",
        SUM(price)   AS "SUM(price)",
        AVG(price)   AS "AVG(price)"
      FROM titles;

distinct

    SELECT
        COUNT(DISTINCT price)
          AS "COUNT(DISTINCT)",
        SUM(DISTINCT price)
          AS "SUM(DISTINCT)",
        AVG(DISTINCT price)
          AS "AVG(DISTINCT)"
      FROM titles;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
        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;

group by

    SELECT
      state,
      COUNT(state), 
      COUNT(*)
    FROM publishers
    GROUP BY state;

    SELECT
      type,
      SUM(sales),
      COUNT(sales), 
      COUNT(*),
      SUM(sales)/COUNT(sales),
      SUM(sales)/COUNT(*),
      AVG(sales)
    FROM titles
    GROUP BY type;

    SELECT
      type,
      SUM(sales),
      AVG(sales),
      COUNT(sales)
    FROM titles
    WHERE price >= 13
    GROUP BY type
    ORDER BY SUM(sales) DESC;

    SELECT
      pub_id,
      type,
      COUNT(*)
    FROM titles
    GROUP BY pub_id, type
    ORDER BY pub_id ASC, COUNT(*) DESC;

case

      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;

HAVING

      SELECT 
        au_id,
        COUNT(*) AS "num_books"
      FROM title_authors
      GROUP BY au_id
      HAVING COUNT(*) >= 3;

      SELECT
        type,
        COUNT(price),
        AVG(price * sales) AS "AVG revenue"
      FROM titles
      GROUP BY type
      HAVING AVG(price * sales) > 1000000;

      SELECT 
        pub_id,
        type,
        COUNT(*)
      FROM titles
      GROUP BY pub_id, type
      HAVING COUNT(*) > 1
      ORDER BY pub_id ASC, COUNT(*) DESC;

      SELECT
        type,
        SUM(sales),
        AVG(price)
      FROM titles
      WHERE pub_id IN ('P03', 'P04')
      GROUP BY type
      HAVING SUM(sales) > 10000
         AND AVG(price) < 20;