More Select Statements

• WHERE field IN (list)

  • SELECT * 
       FROM SKU_DATA 
       WHERE Buyer IN ("Cindy Lo", "Nancy Meyers");
    	 

• Group By
  • Follows WHERE
  • Is primarily designed for use with aggregate functions.

  • SELECT Buyer, Department, COUNT(*)
        FROM SKU_DATA
        GROUP BY Buyer;
    	

  • Notice, some fields might not make sense when you do this.

  • SELECT * 
         FROM SKU_DATA 
         GROUP BY Buyer;
    	

  • SELECT SKU, SUM(QuantityOnHand) AS Count
         FROM INVENTORY
         GROUP BY SKU;
    	

  • Select ... AS aliases can be used

  • SELECT Buyer AS Contact, MAX(SKU)
         FROM SKU_DATA 
         GROUP BY Contact;
    	

  • We can use multiple fields in the GROUP BY statement

  • SELECT Buyer, Department , MAX(SKU)
        FROM SKU_DATA
        GROUP BY Buyer, Department;
    	

  • The SQL book talks about grouping columns. These are the non-aggregated columns.
  • The ROLLUP command allows us to do control break.

  • SELECT WarehouseID, SKU, sum(QuantityOnHand) AS Total
        FROM INVENTORY 
        GROUP BY WarehouseID, SKU WITH ROLLUP;
    	

  • Standard SQL uses GROUP BY ROLLUP expression
  • MySQL lets us add ASC or DESC after a field

  • SELECT WarehouseID, SKU, sum(QuantityOnHand) AS Total
         FROM INVENTORY
         GROUP BY  WarehouseID DESC, SKU WITH ROLLUP;
    	

  • Note, if I change the order of the fields, the command is still right, but it makes less sense.

  • SELECT SKU, WarehouseID, sum(QuantityOnHand) AS Total
          FROM INVENTORY
          GROUP BY  WarehouseID DESC, SKU WITH ROLLUP;
    	

• HAVING
  • Follows GROUP BY
  • Just like the where.

  • SELECT WarehouseID, SKU, sum(QuantityOnHand) AS Total
         FROM INVENTORY
         GROUP BY  WarehouseID DESC, SKU 
         HAVING Total > 500;
    	 

  • Is for use with group by.
• Just for the record, MYSQL has a LIMIT statement

  • SELECT * 
         FROM INVENTORY 
         LIMIT 5;
    	 

  • You can also add an offset

  • SELECT * 
       FROM INVENTORY 
       LIMIT 5 OFFSET 3;