title: Needing assistance with counting the most different books
body: <p>Which customer(s) ordered the most different books? Display the customer id and include ties. This is not talking about the quantity of books ordered. For example, suppose my only orders are Customer 1 ordered 400 copies of book_id 34 Customer 2 ordered 2 copies of book_id  62 and 3 copies of book_id 29 Customer 1 ordered a larger quantity of books but customer 2 ordered more different books. The query should - in this case- return customer 2:</p>
  
  <p>Here is my attempted solution. Not sure how to solve this one:</p>
  
  <pre><code>SELECT cust_id
    FROM a_bkorders.customers
   WHERE cust_id IN 
         (
         SELECT cust_id
           FROM a_bkorders.order_headers
           Join a_bkorders.order_details using (order_id)  
          GROUP BY cust_id
          HAVING count(book_id) &lt;=  
           (
            SELECT MAX(numBooks) 
                    FROM (  
                      SELECT cust_id
                          ,  count(book_id) AS numBooks
                        Join a_bkorders.order_details using (order_id)  
                        FROM a_bkorders.order_headers 
                       GROUP BY cust_id
                        ) t
          )
      )ORDER BY cust_id;
  </code></pre>
  
  <p>Here are the tables:</p>
  
  <pre><code>   -- create customers
      create  table  a_bkorders.customers (
          cust_id           integer          not null 
        , cust_name_last    varchar(20)      not null
        , cust_name_first   varchar(20)      null
        , cust_state        char(2)          not null
        , cust_postal_code  char(10)         not null
        , cust_acct_opened  date             not null 
        , constraint bk_cust_pk              primary key (cust_id)
        , constraint bk_cust_id_range        check (cust_id &gt; 1000)
        , constraint bk_cust_acct_opened_ck  check (cust_acct_opened &gt;=  '1975-01-01' )
      )engine = INNODB;
  
  
  -- create order_headers 
  create  table   a_bkorders.order_headers (
      order_id          integer          not null 
    , order_date        date             not null
    , cust_id           integer          not null 
    , constraint bk_orders_pk            primary key (order_id)
    , constraint bk_orders_cust_fk       foreign key(cust_id) 
                 references a_bkorders.customers(cust_id) 
    , constraint bk_order_id_range       check (order_id &gt; 100)
   ,  constraint bk_order_date_ck        check (order_date &gt;=  '2000-01-01')
  )engine = INNODB;
  
  
  -- create order_details  
  create  table   a_bkorders.order_details (
      order_id          integer          not null 
    , order_line        integer          not null 
    , book_id           integer          not null 
    , quantity          integer          not null 
    , order_price       numeric(6,2)     not null   
    , constraint bk_orderline_pk         primary key (order_id, order_line)
    , constraint bk_orderline_order_fk   foreign key (order_id) 
                 references a_bkorders.order_headers(order_id) on delete cascade
    , constraint bk_orderline_book_fk    foreign key (book_id )  
                 references a_bkinfo.books(book_id)
    , constraint bk_quantity_ck          check (quantity &gt; 0) 
    , constraint bk_ordprice_ck          check (order_price &gt;= 0) 
  )engine = INNODB;
  </code></pre>
  
  <p>Here are the inserts:</p>
  
  <pre><code>-- customers  
      insert into a_bkorders.customers  values  (208950, 'Adams', 'Samuel',        'MA', '02106', '1996-04-15' );
      insert into a_bkorders.customers  values  (200368, 'Blake', 'William',       'CA', '95959', '1997-07-15' );
      insert into a_bkorders.customers  values  (258595, 'Jobs', 'Peter',          'MA', '02575', '1997-01-09' );
      insert into a_bkorders.customers  values  (263119, 'Jones', null,            'IL', '62979', '1997-03-02' );
      insert into a_bkorders.customers  values  (224038, 'Austin', 'Pat',          'CA', '95900', '1997-08-02' );
      insert into a_bkorders.customers  values  (255919, 'Milton', 'John',         'NJ', '08235', '2011-05-31' );
      insert into a_bkorders.customers  values  (211483, 'Carroll', 'Lewis',       'CA', '94203', '1998-08-08' );
      insert into a_bkorders.customers  values  (221297, 'Dodgson', 'Charles',     'MI', '49327', '2001-05-06' );
      insert into a_bkorders.customers  values  (261502, 'Hawthorne', 'Nathaniel', 'MA', '02297', '2001-10-12' );
      insert into a_bkorders.customers  values  (212921, 'Books on Tap', NULL,     'CA', '94112', '2002-01-06' );
      insert into a_bkorders.customers  values  (260368, 'Muller', 'Jonathan',     'IL', '62885', '2005-12-15' );
      insert into a_bkorders.customers  values  (259969, 'Carlsen', 'Benny',       'NJ', '08505', '2011-07-12' );
      insert into a_bkorders.customers  values  (239427, 'Marksa', 'Anna',         'NJ', '08495', '2011-02-28' );
      insert into a_bkorders.customers  values  (296598, 'Collins', 'Douglas',     'MO', '65836', '2005-04-25' );
      insert into a_bkorders.customers  values  (276381, 'Collins', 'Douglas',     'OH', '22451', '2005-02-08' );
      insert into a_bkorders.customers  values  (234138, 'Keats', 'John',          'IL', '61500', '2006-04-30' );
      insert into a_bkorders.customers  values  (267780, 'Shelly', 'Mary',         'CA', '94100', '2010-10-02' );
      insert into a_bkorders.customers  values  (290298, 'Swift', 'Jonathan',      'MI', '49201', '2010-10-12' );
      insert into a_bkorders.customers  values  (226656, 'Randall', 'Randell',     'NJ', '08251', '2011-08-08' );
      insert into a_bkorders.customers  values  (222477, 'Rossetti', 'Christina',  'MI', '49742', '2011-07-11' );
      insert into a_bkorders.customers  values  (227105, 'Kafka', 'Franz',         'MA', '02297', '2010-12-31' );
      insert into a_bkorders.customers  values  (202958, 'Denver', null,           'IL', '60405', '2011-01-15' );
      insert into a_bkorders.customers  values  (218709, 'Bonnard', 'Paul',        'MA', '02558', '2005-11-15' );
      insert into a_bkorders.customers  values  (217796, 'Anders', null,           'IL', '62505', '2011-03-30' );
      insert into a_bkorders.customers  values  (272787, 'Carlson', 'Ben',         'IL', '62505', '2011-05-05' );
      insert into a_bkorders.customers  values  (234709, 'Brahms', 'Johnnie',      'MA', '02558', '2012-01-15' );
      insert into a_bkorders.customers  values  (217002, 'Grieg', 'Edvard',        'IL', '62329', '2012-02-29' );
      insert into a_bkorders.customers  values  (272611, 'Jarrett', 'Keith',       'IL', '62329', '2011-11-11' );
      insert into a_bkorders.customers  values  (299099, 'Sam', 'Dave',            'CA', '94141', '2011-01-01' );
      insert into a_bkorders.customers  values  (259906, 'Capybara', 'Wile E.',    'CA', '94132', '2012-01-05' );
      insert into a_bkorders.customers  values  (259907, 'Hedge', 'Mr.',           'CA', '94132', '2011-09-05' );
      insert into a_bkorders.customers  values  (282716, 'Biederbecke','Dwight',   'PA', '18106', '2013-01-01' );
      insert into a_bkorders.customers  values  (287261, 'Biederbecke','Bix',      'PA', '18106', '2012-01-01' );
      insert into a_bkorders.customers  values  (226275, 'Dalrymple','Jack',       'SD', '57216', '2013-01-01' );
      insert into a_bkorders.customers  values  (228175, 'Cardin','Benjamin',      'MD', '20609', '2013-04-02' );
      insert into a_bkorders.customers  values  (228275, 'Mikulski','Barbara',     'MD', '21203', '2013-04-04' );
      insert into a_bkorders.customers  values  (228352, 'Edwards','Donna',        'MD', '21205', '2013-06-08' );
  
  
  -- orders and  order_details 
  /* July 2011 */
  Insert into a_bkorders.order_headers    values(21841,  '2011-07-02', 267780);
     Insert into a_bkorders.order_details values(21841, 1, 1448, 50,  25.00);
  
  Insert into a_bkorders.order_headers    values(21850,  '2011-07-02', 261502);
     Insert into a_bkorders.order_details values(21850, 1, 1162,  1,  30.49);
     Insert into a_bkorders.order_details values(21850, 2, 1109,  1,  25.00);
  
  Insert into a_bkorders.order_headers    values(2045,  '2011-07-18', 267780);
     Insert into a_bkorders.order_details values(2045, 1, 1894,  1,  35.99);   
  
  Insert into a_bkorders.order_headers    values(2200,  '2011-07-18', 261502);
     Insert into a_bkorders.order_details values(2200, 1, 1200,  5,  16.33);
     Insert into a_bkorders.order_details values(2200, 2, 1180,  5,  45.99);
     Insert into a_bkorders.order_details values(2200, 3, 1128,  5,  46.20);
  
  
  /* Sep 2011 */
  
  Insert into a_bkorders.order_headers    values(22909,  '2011-09-25', 239427);
     Insert into a_bkorders.order_details values(22909, 1, 1104,  5,  45.00);
  
  Insert into a_bkorders.order_headers    values(22910,  '2011-09-25', 218709);
     Insert into a_bkorders.order_details values(22910, 1, 1678,  5,  49.99);
     Insert into a_bkorders.order_details values(22910, 2, 1162,  5,  35.00);
  
  Insert into a_bkorders.order_headers    values(32997,  '2011-09-22', 239427);
     Insert into a_bkorders.order_details values(32997, 1, 1948,  5,  40.94);  
     Insert into a_bkorders.order_details values(32997, 2, 1199,  5,  18.39);
     Insert into a_bkorders.order_details values(32997, 3, 1457,  5,  53.99);
     Insert into a_bkorders.order_details values(32997, 4, 1133,  5,  18.15);
     Insert into a_bkorders.order_details values(32997, 5, 1894,  5,  36.79);
  
  
  Insert into a_bkorders.order_headers    values(32998,  '2011-09-22', 261502);
     Insert into a_bkorders.order_details values(32998, 1, 2006,  3,  20.00);
  
  Insert into a_bkorders.order_headers    values(41005,  '2011-09-28', 290298);
     Insert into a_bkorders.order_details values(41005, 1, 1142,  2,  42.45);
     Insert into a_bkorders.order_details values(41005, 2, 1107,  4,  21.50);
  
  Insert into a_bkorders.order_headers    values(41006,  '2011-09-28', 267780);
     Insert into a_bkorders.order_details values(41006, 1, 1142, 10, 42.95);  
  
  Insert into a_bkorders.order_headers    values(42899,  '2011-09-29', 261502);
     Insert into a_bkorders.order_details values(42899, 1, 1128,  5, 25.00);
     Insert into a_bkorders.order_details values(42899, 2, 1103,  1 , 10.95);  
  
  /* Oct 2011 */
  
  Insert into a_bkorders.order_headers    values(21254,  '2011-10-23', 263119);
     Insert into a_bkorders.order_details values(21254, 2, 2008,  10,   46.95);
     Insert into a_bkorders.order_details values(21254, 3, 2007,  10,   39.00);
  
  Insert into a_bkorders.order_headers    values(21255,  '2011-10-28', 267780);
     Insert into a_bkorders.order_details values(21255, 1, 1101,  5,    59.99);
     Insert into a_bkorders.order_details values(21255, 2, 1142,  5,    39.00);
     Insert into a_bkorders.order_details values(21255, 3, 1162,  2,    35.00);
  
  Insert into a_bkorders.order_headers    values(21261,  '2011-10-28', 200368);
     Insert into a_bkorders.order_details values(21261, 1, 1142,  100,  34.95);
     Insert into a_bkorders.order_details values(21261, 2, 1128,  50,   46.95);
     Insert into a_bkorders.order_details values(21261, 3, 2001,  100,  39.00);
  
  Insert into a_bkorders.order_headers    values(32905,  '2011-10-02', 259906);
     Insert into a_bkorders.order_details values(32905, 1, 2028,  1,  58.00);
  </code></pre>