Introduction to Outer Joins

A short video covering Outer Joins in SQL.

Here is the SQL that goes with the demo.

-- Database by Doug
-- 2/12/2016
-- Intro to Outer Joins

use northwind

-- refresher
-- a join can be restrictive
-- it can filter records that don't satisfy the join

SELECT COUNT(productID) AS [Number of Products]
FROM   Products
-- 79 products

SELECT   COUNT(products.productID) AS [Number of Products]
FROM     Products
   JOIN  Categories ON Products.CategoryID=Categories.CategoryID

-- 77 products joined with Categories?


-- the "ON Products.CategoryID=Categories.CategoryID"
-- is known as the "join condition"

-- we missed the products with the NULL CategoryID

SELECT   ProductID,
         ProductName,
         CategoryID
FROM     Products
ORDER BY CategoryID

-- in particular, these products don't satisfy the join

SELECT   ProductID,
         ProductName,
         CategoryID
FROM     Products
WHERE    CategoryID IS NULL

-- so we get this, 77 products:
SELECT   Products.ProductID,
         Products.ProductName,
         Products.CategoryID,
         Categories.CategoryID,
         Categories.CategoryName
FROM     Products
   JOIN  Categories ON Products.CategoryID = Categories.CategoryID
ORDER BY Products.ProductID

-- that's why there are 77 instead of 79

-- so what if we want to see that categoryID?
--  the boss says she wants to see 
-- *every* product
-- *even if* it has a NULL value for the categoryID

-- in other words
-- "show *every* product, and if that product has a category
--  associated with it, show me that too"

-- this is where an outer join comes in

-- 
-- the above queries have been using a shorthand
-- they are really INNER JOINS
-- by default "JOIN" means "INNER JOIN"
-- the following two queries are logically equivalent
-- and ordering by categoryID

SELECT         Products.ProductID,
               Products.ProductName,
               Products.CategoryID,
               Categories.CategoryID,
               Categories.CategoryName
FROM           Products
       JOIN    Categories ON Products.CategoryID = Categories.CategoryID
ORDER BY       Categories.CategoryID
--
SELECT         Products.ProductID,
               Products.ProductName,
               Products.CategoryID,
               Categories.CategoryID,
               Categories.CategoryName
FROM           Products
   INNER JOIN  Categories ON Products.CategoryID = Categories.CategoryID
ORDER BY       Categories.CategoryID

-- and reformatting ...
-- this is the same as above, just reformatted, more specific
-- this is to get the two tables on the same line
-- and one table is on the left of the "INNER JOIN" phrase
-- and one table is on the right of the "INNER JOIN" phrase

SELECT         Products.ProductID,
               Products.ProductName,
               Products.CategoryID,
               Categories.CategoryID,
               Categories.CategoryName
FROM           Products INNER JOIN Categories  
                  ON Products.CategoryID = Categories.CategoryID
ORDER BY       Categories.CategoryID

-- now, a small change to see the one product
-- that doesn't satisfy the join
-- (at the bottom)

SELECT         Products.ProductID,
               Products.ProductName,
               Products.CategoryID,
               Categories.CategoryID,
               Categories.CategoryName
FROM           Products LEFT JOIN Categories   -- the change is in this line
                  ON Products.CategoryID = Categories.CategoryID
ORDER BY       Categories.CategoryID

-- the LEFT JOIN says:
-- "include all records from the table on the left"
-- in this case, Products is on the left, 
-- so every product record is shown, 
-- even if it doesn't satisfy the join condition

-- of course there is a RIGHT JOIN also
-- the next two queries are logically the same

SELECT         Products.ProductID,
               Products.ProductName,
               Products.CategoryID,
               Categories.CategoryID,
               Categories.CategoryName
FROM           Products LEFT JOIN Categories 
                  ON Products.CategoryID = Categories.CategoryID
ORDER BY       Categories.CategoryID
-- 
SELECT         Products.ProductID,
               Products.ProductName,
               Products.CategoryID,
               Categories.CategoryID,
               Categories.CategoryName
FROM           Categories RIGHT JOIN Products 
                  ON Products.CategoryID = Categories.CategoryID
ORDER BY       Products.ProductID

-- these are actually shorthand for
SELECT         Products.ProductID,
               Products.ProductName,
               Products.CategoryID,
               Categories.CategoryID,
               Categories.CategoryName
FROM           Products LEFT OUTER JOIN Categories 
                  ON Products.CategoryID = Categories.CategoryID
ORDER BY       Categories.CategoryID

-- but the OUTER keyword is typically omitted

-- ** notice that the ON statement does not change
--    across INNER, LEFT, LEFT OUTER, RIGHT, RIGHT OUTER joins

-- OUTER joins are also known as "inclusive" joins,
-- because they *include* records that don't satisfy the join condition

-- INNER joins are also know as "exclusive" joins,
-- because they *exclude* records that don't satisfy the join condition


-- so the above query finds the product with a NULL
-- categoryID
-- the product record doesn't satisfy the join

-- what about a category that doesn't satisfy the join?

SELECT COUNT(CategoryID) AS [Number of Categories in Categories table]
FROM   Categories
-- ten categories

SELECT COUNT(DISTINCT CategoryID) AS [Number of Categories in Products table]
FROM   Products
-- only eight appear in the products table
-- but which 8?
-- we can't look for a NULL foreign key...

-- with few records, we can inspect the actual records...

SELECT   CategoryID
FROM     Categories
ORDER BY CategoryID

SELECT   DISTINCT CategoryID
FROM     Products
ORDER BY CategoryID

-- but what if there are thousands of categories
-- and millions of products?

-- an outer join..
-- from the other side...
-- "inclusive" of all Categories
SELECT   Categories.CategoryName,
         Categories.CategoryID,
         Products.CategoryID,
         Products.productID,
         Products.productName
FROM     Products RIGHT JOIN Categories 
            ON Products.CategoryID= Categories.CategoryID
ORDER BY Categories.CategoryID,
         Products.ProductID

-- so this includes *every* category, 
-- even the ones that don't satisfy the join
-- and also shows the category/product combinations that do satisfy the join

-- a common use of an outer join is 
-- to find records in one table that don't match
--  records from another table

-- this is sometimes called a "find unmatched" query

-- looking at the previous query again...

SELECT   Categories.CategoryName,
         Categories.CategoryID,
         Products.CategoryID,
         Products.productID,
         Products.productName
FROM     Products RIGHT JOIN Categories 
            ON Products.CategoryID= Categories.CategoryID
ORDER BY Categories.CategoryID,
         Products.ProductID

-- and rephrasing it as a LEFT JOIN...

SELECT   Categories.CategoryName,
         Categories.CategoryID,
         Products.CategoryID,
         Products.productID,
         Products.productName
FROM     Categories LEFT JOIN Products 
            ON Products.CategoryID= Categories.CategoryID
ORDER BY Categories.CategoryID,
         Products.ProductID

-- note the two categories with no matching products
-- they both have a NULL primary key, which can't be, right?
-- which is a sign that there really is no product record
-- so let's limit to only that 
-- to limit to only the categories with no products

SELECT   Categories.CAtegoryName,
         Products.productID,
         Products.productName
FROM     Categories LEFT JOIN  Products
            ON Products.CategoryID= Categories.CategoryID
WHERE    Products.productID IS NULL
ORDER BY Categories.CAtegoryName,
         Products.productName

-- and formatting for readability
SELECT      Categories.CAtegoryName,
            Products.productID,
            Products.productName
FROM        Categories 
  LEFT JOIN Products ON Products.CategoryID = Categories.CategoryID
WHERE       Products.productID IS NULL
ORDER BY    Categories.CategoryName,
            Products.productName

-- and now, a FULL OUTER JOIN
-- this shows *every* category
-- and *every* product
-- even the ones that don't satisfy the join
-- its the left and the right together

SELECT   Categories.CategoryName,
         Categories.CategoryID,
         Products.CategoryID,
         Products.productID,
         Products.productName
FROM     Products FULL OUTER JOIN Categories 
            ON Products.CategoryID= Categories.CategoryID
ORDER BY Categories.CAtegoryName,
         Products.productName

-- and formatted consistently with the above examples

SELECT               Categories.CategoryName,
                     Categories.CategoryID,
                     Products.CategoryID,
                     Products.productID,
                     Products.productName
FROM                 Products 
   FULL OUTER JOIN   Categories ON Products.CategoryID= Categories.CategoryID
ORDER BY             Categories.CAtegoryName,
                     Products.productName


-- Database by Doug
-- 2/12/2016
-- Intro to Outer Joins

Group By Part 3: uniqueness and multi-column grouping

A short video covering the GROUP BY clause in SQL.

Part 3: uniqueness and multi-column grouping
For beginners.

Here is the SQL that goes with the demo.

-- Database by Doug
-- Douglas Kline
-- 2/4/2016
-- GROUP BY - Part 3 uniqueness and multi-column Grouping

USE Northwind

-- here's a basic query joining Categories with Products
SELECT   Categories.CategoryName,
         Products.ProductName,
         Products.Unitprice
FROM     Products
  JOIN   Categories    ON Products.CategoryID = Categories.CategoryID
ORDER BY Categories.CategoryName

-- now let's aggregate
-- show the average unit price, by Category

SELECT   Categories.CategoryName,
         AVG(Products.unitprice) AS [average unit price]
FROM     Products
  JOIN   Categories    ON Products.CategoryID = Categories.CategoryID
GROUP BY Categories.CategoryName
ORDER BY Categories.CategoryName

-- awesome! runs fine, looks reasonable...

-- or is it

-- let me add one more item
SELECT   Categories.CategoryName,
         AVG(Products.unitprice) AS [average unit price]
FROM     Products
  JOIN   Categories    ON Products.CategoryID = Categories.CategoryID
GROUP BY Categories.CategoryName,
         Categories.CategoryID -- this is the added piece
ORDER BY Categories.CategoryName

-- notice that now I have an additional group
-- let's add a column to see what's going on
SELECT   Categories.CategoryID,
         Categories.CategoryName,
         AVG(Products.unitprice) AS [average unit price]
FROM     Products
  JOIN   Categories    ON Products.CategoryID = Categories.CategoryID
GROUP BY Categories.CategoryName,
         Categories.CategoryID -- this is the added piece
ORDER BY Categories.CategoryName

-- aha! there are two categories with the same name
-- is that OK? 
-- well, CategoryName is not the primary key
-- and apparently the schema allows duplicate values in the CategoryName

-- so, the lesson here is to always specify a *unique* grouping
-- in general, this means adding a primary key 
-- to the GROUP BY statement, even if it is not displayed

-- for example:
SELECT   Categories.CategoryName,
         AVG(Products.unitprice) AS [average unit price]
FROM     Products
  JOIN   Categories    ON Products.CategoryID = Categories.CategoryID
GROUP BY Categories.CategoryName,
         Categories.CategoryID 
ORDER BY Categories.CategoryName,
         Categories.CategoryID

-- or another example
SELECT   Suppliers.CompanyName,
         AVG(Products.unitprice) AS [average unit price]
FROM     Products
   JOIN  Suppliers      ON Products.SupplierID = Suppliers.SupplierID
GROUP BY Suppliers.CompanyName,
         Suppliers.SupplierID
ORDER BY Suppliers.CompanyName,
         Suppliers.SupplierID

-- notice in the second example that there are no duplicate
-- company names
-- so should we still add the SupplierID to the group by?
-- absolutely
-- your query needs to work on any data that might be in the database
-- not just what's there now

-- now for multi-level grouping
-- consider this query

SELECT   Suppliers.CompanyName,
         Categories.CategoryName,
         Products.ProductName,
         Products.Unitprice
FROM     Suppliers
   JOIN  Products    ON Suppliers.SupplierID =Products.SupplierID
   JOIN  Categories  ON Categories.CategoryID=Products.CategoryID
ORDER BY Suppliers.CompanyName,
         Categories.CategoryName

-- notice that Exotic Liquids supplies us products in two categories
-- same with Forets...

-- so, let's calculate average unitprice by Supplier AND Category

SELECT   Suppliers.CompanyName,
         Categories.CategoryName,
         AVG(Products.Unitprice)  AS [avg unit price]
FROM     Suppliers
   JOIN  Products    ON Suppliers.SupplierID =Products.SupplierID
   JOIN  Categories  ON Categories.CategoryID=Products.CategoryID
GROUP BY Suppliers.CompanyName,
         Categories.CategoryName
ORDER BY Suppliers.CompanyName,
         Categories.CategoryName

-- notice that now Exotic liquids has two groups
-- one for the Beverages category 
-- and one for the Spreads category

-- so now, the grouping is based on the *combination* of the two fields
-- note that the order of the fields in the GROUP BY doesn't matter
-- but for clarity it is best to be consistent with the ORDER BY 
--  (the order of the fields in the ORDER BY *does* matter)

-- and of course, to make sure our groupings are *unique* we should add
-- a unique field to the GROUP BY

SELECT   Suppliers.CompanyName,
         Categories.CategoryName,
         AVG(Products.Unitprice)  AS [avg unit price]
FROM     Suppliers
   JOIN  Products    ON Suppliers.SupplierID =Products.SupplierID
   JOIN  Categories  ON Categories.CategoryID=Products.CategoryID
GROUP BY Suppliers.CompanyName,
         Categories.CategoryName,
         Suppliers.SupplierID,
         Categories.CategoryID
ORDER BY Suppliers.CompanyName,
         Categories.CategoryName,
         Suppliers.SupplierID,
         Categories.CategoryID

-- on a side note
-- it's also important to have a unique ordering
-- so that downstream consumers of your data aren't surprised
-- when the ordering changes

-- note that very different information that is provided if 
-- the column-ordering, and the row-ordering is reversed

SELECT   Categories.CategoryName,
         Suppliers.CompanyName,
         AVG(Products.Unitprice)  AS [avg unit price]
FROM     Suppliers
   JOIN  Products    ON Suppliers.SupplierID =Products.SupplierID
   JOIN  Categories  ON Categories.CategoryID=Products.CategoryID
GROUP BY Categories.CategoryName,
         Suppliers.CompanyName,
         Suppliers.SupplierID,
         Categories.CategoryID
ORDER BY Categories.CategoryName,
         Suppliers.CompanyName,
         Suppliers.SupplierID,
         Categories.CategoryID

-- all the data in the result set is the same as the previous example

-- in the first example, the focus was on the Supplier
-- and secondarily, on the categories

-- in the second example, the focus is on the Category
-- and secondarily, on the Suppliers

-- in summary, make sure that your GROUP BY statement
-- specifies a unique grouping
-- this is generally done by adding the primary key

-- you can specify multiple columns in the GROUP BY
-- then groups are determined by *unique combinations*
-- of the values in the columns

-- finally, merely changing the column ordering
-- and the row ordering can produce quite different
-- information for human use

-- Database by Doug
-- Douglas Kline
-- 2/4/2016
-- GROUP BY - Part 3 uniqueness & multi-column Grouping

Group By Part 2: Null Handling and Count

A short video covering the GROUP BY clause in SQL.

Part 2: covering how NULL values are handled and Counts For beginners.

Here is the SQL that goes with the demo.

-- Database by Doug
-- Douglas Kline
-- 2/4/2016
-- GROUP BY - Part 2 NULL Handling & Count
USE Northwind

-- see GROUP BY - Part 1 Introduction
-- what about NULL values? How are they handled?

SELECT SUM(unitprice)/COUNT(ProductID)                AS [manual average unitprice],
       AVG(unitprice)                                 AS [correct average unitprice],
       SUM(unitprice)/COUNT(ProductID)-AVG(unitprice) AS [difference]
FROM   products

-- normally, you would expect these two averages to agree
-- but I've added a product with a NULL unitprice:

SELECT *
FROM   Products
WHERE  unitprice IS NULL

-- so let's look at all the pieces to see what's happening

SELECT SUM(unitprice)                  AS [total of all unitprices],
       COUNT(ProductID)                AS [number of records],
       SUM(unitprice)/COUNT(ProductID) AS [manual average unitprice],
       AVG(unitprice)                  AS [correct average unitprice]
FROM   products

-- the problem here is that there are 79 total records
-- but only 78 non-NULL unitprices
-- the AVG function handles the NULL appropriately
-- and only divides by 78

-- in general, only non-NULL values are included in MIN, MAX, SUM, STDEV, VAR, AVG, etc.

-- SO, depend on the functions, rather than writing your own
-- they will appropriately handle NULL values
SELECT      AVG(unitprice),
            SUM(unitprice),
            STDEV(unitprice),
            VAR(unitprice)
FROM        Products

-- COUNT needs more explanation
-- note the difference in the two number returned below:

SELECT   COUNT(ProductID)   AS [number of non-NULL productIDs],
         COUNT(unitprice)   AS [number of non-NULL unitPrices]
FROM     Products

-- COUNT requires a field to be specified
-- it will count records that have a non-NULL value in that field

-- but what about this?

SELECT   COUNT(*)          AS [count of products]
FROM     Products

-- recall that an asterisk in the SELECT clause is shorthand for 'all fields'
-- so the above can be interpreted as 
-- "check all fields for non-NULL values, and count if any field has a non-NULL value"

-- of course, if a table has a primary key, then the table can be guaranteed
-- to have at least one field that is non-NULL (the primary key field)

-- for code clarity, the asterisk should be avoided
-- it is much clearer to count the primary key of the entity that you intend to count

-- as an example:
SELECT         COUNT(*)    AS [what is being counted here?]
FROM           Products  
   INNER JOIN  Categories  ON Products.CategoryID = Categories.CategoryID

-- this is much clearer
-- we're counting products, because we are counting the primary key
-- of the products table
SELECT         COUNT(Products.ProductID) AS [product count]
FROM           Products  
   INNER JOIN  Categories  ON Products.CategoryID = Categories.CategoryID

-- but why are their only 77 records, and not 79?
-- because only 77 products satisfy the JOIN with Categories:

SELECT      ProductID,
            CategoryID
FROM        Products
ORDER BY    CategoryID

-- Database by Doug
-- Douglas Kline
-- 2/4/2016
-- GROUP BY - Part 2 NULL Handling & Count

GROUP BY Part 1: Introduction

A short video covering the GROUP BY clause in SQL.

For beginners.

Here is the SQL that goes with the demo.

-- Database by Doug
-- Douglas Kline
-- 2/4/2016
-- GROUP BY - Part 1 Introduction

USE Northwind

-- here's a simple SELECT statement from the Products table

SELECT   ProductID,
         productname,
         unitprice,
         UnitsInStock
FROM     Products
ORDER BY ProductID

-- notice that I get all the rows, because there is not a WHERE clause

-- if there were 10 million rows, I would see all of them

-- but sometimes I'd like to see a *summary* of the products

-- consider this:

SELECT   AVG(unitprice)  AS [average unit price]
FROM     Products

-- in this case, we have summarized all the prices of all products
-- into a single number -  the average

-- this summarization is known as "aggregating" or "aggregation"
-- we aggregate all the product records into a single group,
-- then we can ask questions about the group

-- what else can we find out about the group?

SELECT   AVG(unitprice)    AS [average unit price],
         MIN(unitprice)    AS [minimum unit price],
         MAX(reorderlevel) AS [maximum reorder level],
         COUNT(productID)  AS [number of products],
         SUM(unitsinStock) AS [total number of units in stock],
         STDEV(unitprice)  AS [standard deviation of unit price],
         VAR(unitprice)    AS [variance of unit price]
FROM     Products

-- we can also include expressions in an aggregate function

SELECT   SUM(unitprice * unitsInStock) AS [total value of inventory]
FROM     Products



-- in the above examples we were aggregating over the whole table
-- but you can limit the aggregation

SELECT   AVG(UnitPrice)     AS [average unit price]
FROM     Products
WHERE    CategoryID IN (2, 4, 6)

-- in this example, the AVG is calculated over 
-- products in the stated categories

-- think of the above example as happening in this order
-- 1 - the WHERE clause - records are eliminated first
-- 2 - the averaging - aggregate calculated over remaining records

-- now consider these two queries:
SELECT   AVG(unitprice) AS [Avg price of category 1 products]
FROM     Products
WHERE    categoryID = 1

SELECT   AVG(unitprice) AS [Avg price of category 2 products]
FROM     Products
WHERE    categoryID = 2

-- wouldn't it be nice to have these in the same table?
-- or a table that shows a list of categories, and the average for each category?
-- like this:
--**********************
--* CategoryID *  Avg  *
--**********************
--*         1  *  31.8 *
--*         2  *  23.1 *
--*         3  *  15.2 *
--*       ...  *  ...  *
--**********************

-- here it is:

SELECT   CategoryID,
         AVG(unitprice) AS [Avg unitprice]
FROM     Products
GROUP BY CategoryID
ORDER BY CategoryID

-- the new part is the GROUP BY clause
-- it has to be before the ORDER BY, and after the WHERE

-- the logic is this:
-- arrange the products into groups, using the CategoryID
-- all CategoryID =1 records go into a group
-- all CategoryID =2 records go into a group
-- etc.
-- now, answer a question about each group - what is the average unit price?

SELECT   CategoryID,
         AVG(unitprice) AS [Avg unitprice]
FROM     Products
GROUP BY CategoryID
ORDER BY CategoryID

-- and once the grouping is established, I can ask other aggregate questions:

SELECT   CategoryID,
         AVG(unitprice)    AS [average unit price],
         MIN(unitprice)    AS [minimum unit price],
         MAX(reorderlevel) AS [maximum reorder level],
         COUNT(productID)  AS [number of products],
         SUM(unitsinStock) AS [total number of units in stock],
         STDEV(unitprice)  AS [standard deviation of unit price],
         VAR(unitprice)    AS [variance of unit price]
FROM     Products
GROUP BY CategoryID
ORDER BY CategoryID
-- 
-- so how can we visualize what's going on?

SELECT   CategoryID
FROM     Products
ORDER BY CategoryID

-- in the above, notice the groupings of
-- the records, by categoryID

-- first two records are one group, with a NULL categoryID
-- next twelve records are the next group, where categoryID=1
-- etc.

-- now group them
SELECT   CategoryID
FROM     Products
GROUP BY CategoryID
ORDER BY CategoryID

-- each of the 9 resulting records represents
-- a group of products that have a common categoryID

-- now there are groups of products
-- one group for each different categoryID
-- (you can also do this with DISTINCT,
--  but that's for another video)

-- now let's add some attributes that we're interested in
SELECT   CategoryID,
         ProductID,
         ProductName,
         Unitprice
FROM     Products
ORDER BY CategoryID,
         ProductID

-- looking at the left-most column,
-- we can see values that will become groups
-- the two NULL categoryIDs will become a group
-- the next twelve records with categoryID=1 will become the next group

-- we're looking to get the average unit price by category
-- so let's pare down to just the columns we need
SELECT   CategoryID,
         Unitprice
FROM     Products
ORDER BY CategoryID

-- so let's "convert" the above into an aggregate
-- and I'm expecting that there will be two products for categoryID NULL
-- and twelve products for categoryID=1

SELECT   CategoryID,
         AVG(unitprice) AS [average unit price] -- the AVG function is new
FROM     Products
GROUP BY CategoryID    -- the GROUP BY is new
ORDER BY CategoryID

-- the changes above are:
-- * add the GROUP BY clause with the CategoryID
-- * add the AVG function to the unitprice

-- in the english language, a common idiom is to ask for
-- "a count of products by category" or
-- "the average unit price by supplier"
-- when someone asks "... by category" 
-- they usually mean that the category should be in the leftmost column
-- and that the records should be GROUP BY'ed category
-- and that the records should be ORDER BY'ed category

-- a couple of examples

-- Manager Bob: "show me the total value of inventory by category"
-- here's what Manager Bob most likely wants (but avoiding JOINs for now):
SELECT      CategoryID,     
            SUM(unitprice * unitsinstock) AS [value of inventory]
FROM        Products
GROUP BY    CategoryID
ORDER BY    CategoryID

-- Manager Barb: "show me the lowest unitprice of products by supplier"
-- here's what Manager Barb most likely wants (but avoiding JOINs again...):

SELECT      SupplierID,
            MIN(unitprice) AS [lowest unitprice]
FROM        Products
GROUP BY    SupplierID
ORDER BY    SupplierID

-- more clearly
-- "by Supplier"
SELECT      SupplierID,    -- supplier in first column
            MIN(unitprice) AS [lowest unitprice]
FROM        Products       
GROUP BY    SupplierID     -- supplier is the basis for grouping
ORDER BY    SupplierID     -- records ordered by supplier



-- throwing in a JOINs for clearer suppliers:
SELECT      Suppliers.CompanyName,
            MIN(Products.unitprice) AS [lowest unitprice]
FROM        Products
   JOIN     Suppliers      ON Products.SupplierID = Suppliers.SupplierID
GROUP BY    Suppliers.CompanyName,
            Suppliers.SupplierID   -- this is important, but not covered here
ORDER BY    Suppliers.CompanyName
 

-- Database by Doug
-- Douglas Kline
-- 2/4/2016
-- GROUP BY introduction