Grouping in SQL Select Statements

• Group Byfield

•  
  Select Department, count(*) 
  From SKU_DATA 
  Group by Department;
  

• Careful with this one, it seems a bit tricky

• Select * 
  From INVENTORY 
  Group By SKU;
      

• Produces a meaningless report

• select SKU, avg(QuantityOnHand) from INVENTORY Group By SKU;
   

• Gives the average QuantityOnHand by SKU;
• The following is supposed to produce an error:

• Select SKU, Department, Count(*) 
  From SKU_DATA 
  Group by Buyer;
      

• Since it will require the DBMS to combine all SKUs from a department into a single entry in the output table.
• It works in MySQL, apparently selecting the first SKU encountered.
• In general, the fields selected for display must be listed in the group by clause.

• Select Department, count(*) as BuyerCount 
  From SKU_DATA 
  Where Department <> "Climbing"
  Group by Department 
  Order by BuyerCount Desc;
      

• The author suggests the Where clause should be given before the Group By
• The having clause gives us another way to sort data.

• Select Department, count(*) 
  From SKU_DATA 
  Where SKU <> 302000  
  Group By Department 
  Having count(*) > 1 
  Order by count(*);