SQL: from WHERE to HAVING

WHERE filters results before they are grouped.
HAVING filters results after they are grouped.

correct:

 count players by team:
   SELECT  COUNT(*) count,  plheight_ft 
     FROM  ALplayers 
    WHERE  plheight_ft = 6
 GROUP BY  plheight_ft
 ORDER BY  count  

error:

   SELECT  COUNT(*) count,  plheight_ft 
     FROM  ALplayers 
    WHERE  count = 6      // <-------- error! 
 GROUP BY  plheight_ft
 ORDER BY  count  

correct:

   SELECT  COUNT(*) count,  plheight_ft 
     FROM  ALplayers 
 GROUP BY  plheight_ft
   HAVING  count = 6  
 ORDER BY  count  

we want to see if/how-many people have the same name:
(remove duplicate records)

1st, just look at a few:

   SELECT plplyrid, plNamef, plNamel, plpaddress
     FROM ALplayers
    WHERE plgameyear = '2006'
 ORDER BY plNamel, plNamef
    LIMIT 0, 44  

…there may be about 4 duplicates and about 10+ brothers/cousins

   SELECT  COUNT(*) as count, plplyrid, plNamef, plNamel, 
           concat(substring(plNamef, 1, 1), plNamel) as ckname,
           plpaddress
     FROM  ALplayers 
    WHERE  plgameyear = '2006'
 GROUP BY  ckname
   HAVING  count > 1  
 ORDER BY  ckname  

( — o.k. — I saw 7 dups.(2 each) )

now, to see those people:
1st way: join table to itself.

   SELECT  DISTINCT p1.plplyrkey, p1.plplyrid, p1.plNamef, p1.plNamel, 
           concat(substring(p1.plNamef, 1, 1), p1.plNamel) as ckname,
           p1.plpaddress, p1.plschlkey, p1.plheight_ft
     FROM  ALplayers as p1,  ALplayers as p2
    WHERE  p1.plgameyear = '2006'
      AND  p2.plgameyear = '2006'
      AND  concat(substring(p1.plNamef, 1, 1), p1.plNamel) = 
           concat(substring(p2.plNamef, 1, 1), p2.plNamel)
      AND  p1.plplyrid != p2.plplyrid  
 ORDER BY  ckname   

— ahha, 3 of the 7 are brothers or cousins with the same 1st initial.

now, to see those people again:
2nd way: sub-query. (works in mysql 5.x)

   SELECT  p1.plplyrkey, p1.plplyrid, p1.plNamef, p1.plNamel, 
           concat(substring(p1.plNamef, 1, 1), p1.plNamel) as ckname,
           p1.plpaddress, p1.plschlkey, p1.plheight_ft
     FROM  ALplayers as p1
    WHERE  p1.plgameyear = '2006'
    HAVING  ckname  IN
          (SELECT  concat(substring(p2.plNamef, 1, 1), p2.plNamel) as p2ckname
             FROM  ALplayers as p2
            WHERE  p2.plgameyear = '2006'
         GROUP BY  p2ckname
           HAVING  count(*) > 1 ) 
   ORDER BY  ckname   

# display duplicate records, if there are any (only works in mysql 5.x) :

    SELECT  h1.orderid
      FROM  oltohead as h1
    HAVING  orderid  IN
          (SELECT  h2.orderid
             FROM  oltohead as h2
         GROUP BY  h2.orderid
           HAVING  count(*) > 1 ) 
  ORDER BY  h1.orderid   

To find the customers with total orders of less than 100.

   SELECT  Customer,  SUM(OrderPrice)  
     FROM  Orders
 GROUP BY  Customer
   HAVING  SUM(OrderPrice) < 100   

To find if the customers "Jones" or "Smith" have total orders of more than 300.

    SELECT  Customer,  SUM(OrderPrice) 
      FROM  Orders
     WHERE  Customer='Jones'
        OR  Customer='Smith'
  GROUP BY  Customer
    HAVING  SUM(OrderPrice) > 300   

The HAVING clause can refer to aggregate functions, which the WHERE clause cannot:

     SELECT  user, MAX(salary) 
       FROM  users
   GROUP BY  user 
     HAVING  MAX(salary) > 10;  

To find duplicates on a field that hasn't been uniquely indexed, you can do this:

   SELECT  BookISBN, count(BookISBN) 
     FROM  Books 
 GROUP BY  BookISBN HAVING COUNT(BookISBN) > 1;