High Tech Hope Video Notes

Video 1 - Introduction to Databases

Agenda

Why should I learn this stuff?
Database definitions
I don't have a server!

Why should I learn this stuff?

Everything is stored electronically; a lot of it is stored in databases
Maybe there is information stored in there that you need to automate your work

Database Definitions

Database

An organized way to store data in tables and columns

RDBMS

Relational Database Management System

SQL Server

Microsoft's RDBMS

Oracle

Another RDBMS

SQL Server Management Studio

SSMS

Tool we use to work with SQL Server

OLTP Database

Online transaction processing database

I.E. buying something from a website, adding a new employee to the system

A database where the work is happening

More Definitions

OLAP Database

Online Analytical Processing

An efficient reporting database

Might be called a data warehouse

Data is stored differently than an OLTP database

Normalized

Making database efficient for inserting and updating new data

SQL

Structured Query Language

Standard language used to manipulate databases

Every database system adds its own additional features to it

T-SQL

SQL Server's version of SQL

Transact SQL; Microsoft's version of SQL

Table

How we store data

Every properly designed database has a table for each object

Object

Grid of columns and rows

Column

How we setup our data

Can apply rules to them

Row

Information about a particular instance

I.E. a row for each customer

Even more...

Primary Key

A column, one or a combination of columns, that uniquely defines the row

Foreign Key

A column in a table that points to the primary key in another table

I.E. Order table has one column, CustomerID, that points back to one row in the Customer table

Index

Similar to an index in the back of a book

Quick search to jump right to the page (or row) you want

Query

Retrieve or manipulate data in our database

Four types of queries:

SELECT

Used to retrieve data

INSERT

Inserting new rows into our table

UPDATE

Modify existing rows

DELETE

Remove existing rows

I don't have a server!

Search for "SQL Server Virtual Labs"
You can take them for a spin for free
Live in the cloud
Created on the fly automatically
When you close it up, the virtual computer is destroyed
Use IE & do bing search for SQL Server Virtual Labs
Look for one that has the word "report" in it
Hotlaunchpad?
Opens a VM and a manual
Scroll until you find the info on how to login to the VM
(Mine automatically logged me in)
Had to read doc instructions because her instructions were different to launch SQL Server 2008 R2 Management Studio
My VM has 2012?
She goes to the start menu, all programs, SQL Server 2008 R2, SQL Server 2008 R2 Management Studio
Hit Connect button
Server name: DEMO2010A
Expand Database
AdventureWorks2008R2 & AdventureWorksDW2008R2 are the ones to work with
Expand, Tables, HumResources.Employee, Right Click, Select Top 1000 Rows
Query window pops open
Top shows SELECT statement, bottom shows table

For more information...

Video 2 - SELECT Part 1

Agenda

Tools
so we can follow along or work on our own to learn more
Hello, World!
Retrieving Data
Filtering Data
Ordering Data

Tools

SQL Server
If you have a SQL Server available, download the sample OLTP database, AdventureWorks, from www.Codeplex.com
Figure out which version of SQL server you are running and download that specific version
You can start with anything from 2008 to 2012 to follow along

Tools

SQL Server free virtual labs
http://www.microsoft.com/sqlserver/en/us/learning-center/virtual-labs.aspx
Go back to first video to learn more on how to do this

Demos, and More Demos!

She goes to the start menu, all programs, SQL Server 2008 R2, SQL Server 2008 R2 Management Studio
AdventureWorks2008R2 is the ones to work with
(I went to MSBI box to get into SSMS)
How to print something on the screen
Click New Query
Select line you want to run, otherwise everything will run
Typed in PRINT 'Hello, World!'
Highlight it
Press Execute button
Hello, World! displays in Messages tab
Type in SELECT 'Hello, World!'
Highlight it
Execute
This time the results come back in a grid
There are buttons up top that let you select whether you want the results to go to text, grid or a file
Selected text and you get text in a Results tab at bottom of screen
Type in

USE AdventureWorks2012
GO

1. the word SELECT, (* in this example)
2. the word FROM, followed by the table name

SELECT * FROM HumanResources.Department

SELECT * FROM Department

USE [AdventureWorks2012]
GO

SELECT [DepartmentID]
      ,[Name]
      ,[GroupName]
      ,[ModifiedDate]
FROM [HumanResources].[Department]
GO

SELECT [BusinessEntityID]
     , [JobTitle]
     , [BirthDate]
     , [MaritalStatus]
     , [Gender]
     , [HireDate]
FROM [HumanResources].[Employee]

WHERE [BusinessEntityID] = 3

WHERE 1 = 1

WHERE 1 = 2

SELECT [BusinessEntityID]
     , [JobTitle]
     , [BirthDate]
     , [MaritalStatus]
     , [Gender]
     , [HireDate]
FROM [HumanResources].[Employee]
WHERE Gender = 'F'

SELECT [BusinessEntityID]
     , [JobTitle]
     , [BirthDate]
     , [MaritalStatus]
     , [Gender]
     , [HireDate]
FROM [HumanResources].[Employee]
WHERE BirthDate BETWEEN '1/1/1970' AND '12/31/1970'

SELECT [BusinessEntityID]
     , [JobTitle]
     , [BirthDate]
     , [MaritalStatus]
     , [Gender]
     , [HireDate]
FROM [HumanResources].[Employee]
ORDER BY [BirthDate]

ORDER BY [HireDate]

ORDER BY [HireDate] DESC

ORDER BY [JobTitle], [BirthDate] DESC

We've learned a lot in this video. We've learned how to:

Find where our tables are

Look at them over in the sidebar

Connect to a specifc database

Print a message on the screen

Select information from a table using a SELECT statement

Filter data

Sort data

Video 3 - SELECT Part 2

How to use expressions and functions in your T-SQL statements

Agenda

Tools
Concatenate Columns
Mathematical Operations
Using Functions
CASE

Tools

SQL Server
If you have a SQL Server available, download the sample OLTP database, AdventureWorks, from www.Codeplex.com
Figure out which version of SQL server you are running and download that specific version
You can start with anything from 2008 to 2012 to follow along

Tools

SQL Server free virtual labs
http://www.microsoft.com/sqlserver/en/us/learning-center/virtual-labs.aspx
Go back to first video to learn more on how to do this

Concatenate two columns

--Concatenating character data
SELECT Name, GroupName,
[Name] + [GroupName]
FROM HumanResources.Department

--Adding an alias
SELECT Name, GroupName,
[Name] + [GroupName] AS "New Name"
FROM HumanResources.Department

SELECT Name AS "Old Name", GroupName,
[Name] + [GroupName] AS "New Name"
FROM HumanResources.Department

SELECT Name GroupName,
[Name] + [GroupName] AS "New Name"
FROM HumanResources.Department

--Add a literal value
SELECT Name OldName, GroupName,
[GroupName] + ': ' + [Name] AS "New Name"
FROM HumanResources.Department

--Add a number to a string
SELECT Name + DepartmentID
FROM HumanResources.Department

--Cast and Convert
SELECT Name + CAST(DepartmentID AS VARCHAR) AS "Cast",
Name + CONVERT(VARCHAR, DepartmentID) AS "Convert"
FROM HumanResources.Department

--NULLS
SELECT AddressLine1, AddressLine2
FROM Person.Address

SELECT AddressLine1, AddressLine2,
AddressLine1 + ' ' + AddressLine2
FROM Person.Address

--ISNULL and COALESCE
SELECT AddressLine1, AddressLine2,
AddressLine1 + ' ' + ISNULL(AddressLine2,'') AS "ISNULL",
AddressLine1 + ' ' + COALESCE(AddressLine2,'') AS "COALESCE"
FROM Person.Address

--More COALESCE
SELECT Size, Class, Color
    ,COALESCE(Size,Class,Color) AS "COALESCE"
FROM Production.Product
WHERE Color IS NOT NULL OR Size IS NOT NULL
OR Class IS NOT NULL

/* NEW FUNCTION IN SQL SERVER 2012!

CONCAT(Co11,Co12,Co13)

*/

--Mathematical operators
SELECT BusinessEntityID,
BusinessEntityID * 2 AS "Multiply",
BusinessEntityID / 2 AS "Divide",
BusinessEntityID + 2 AS "Add",
BusinessEntityID - 2 AS "Subtract",
BusinessEntityID % 2 AS "Modulo"
FROM HumanResources.Employee

--Mathematical functions
SELECT BusinessEntityID,
SQUARE(BusinessEntityID) AS "Square",
SQRT(BusinessEntityID) AS "SQRT",
ROUND(SQRT(BusinessEntityID),2) AS "Round"
FROM HumanResources.Employee

--String functions
SELECT Description, LEFT(Description,10) AS "Left 10",
SUBSTRING(Description,11,30) AS "Substring",
CHARINDEX('Alu',Description) AS "Charindex"
FROM Production.ProductDescription

--Date functions
SELECT BirthDate, HireDate,
DATEADD(yyyy,10,HireDate) AS "DateAdd",
DATEDIFF(YYYY,BirthDate,HireDate) AS "DateDiff",
YEAR(BirthDate) AS "Year",
GETDATE()
FROM HumanResources.Employee

/* NEW FUNCTIONS in SQL Server 2012!!!

FORMAT, EOMONTH, DATEFROMPARTS

*/

--Convert
SELECT BirthDate,
CONVERT(VARCHAR,BirthDate,111) AS "Slashes",
CONVERT(VARCHAR,BirthDate,107) AS "Spelled out"
FROM HumanResources.Employee

--CASE
SELECT BusinessEntityID,
CASE BusinessEntityID % 2 WHEN 0 THEN 'Even'
WHEN 1 THEN 'Odd' Else 'UNKNOWN' END AS OddOREven
FROM HumanResources.Employee

/* NEW FUNCTIONS IN SQL SERVER 2012

IIF

*/

--Order by
SELECT HireDate
FROM HumanResources.Employee
ORDER BY MONTH(HireDate)

Video 4 - SELECT Part 3

In this video, we'll learn more about the WHERE clause

Agenda

Tools
Using functions and expressions
NULL
Multiple conditions
The IN list

Tools

SQL Server
If you have a SQL Server available, download the sample OLTP database, AdventureWorks, from www.Codeplex.com
Figure out which version of SQL server you are running and download that specific version
You can start with anything from 2008 to 2012 to follow along

Tools

SQL Server free virtual labs
http://www.microsoft.com/sqlserver/en/us/learning-center/virtual-labs.aspx
Go back to first video to learn more on how to do this

Demos, and More Demos!

--Using an expression in the WHERE clause
SELECT BusinessEntityID
FROM HumanResources.Employee
WHERE BusinessEntityID % 2 = 0

--Using an expression in the WHERE clause
SELECT BusinessEntityID
FROM HumanResources.Employee
WHERE BusinessEntityID % 2 != 0

--Using a function in the WHERE clause
SELECT BusinessEntityID, HireDate
FROM HumanResources.Employee
WHERE YEAR(HireDate) = 2003

--A better way to write this!
SELECT BusinessEntityID, HireDate
FROM HumanResources.Employee
WHERE HireDate >= '1/1/2003' AND HireDate < '1/1/2004'

--BETWEEN
SELECT BusinessEntityID, HireDate
FROM HumanResources.Employee
WHERE HireDate BETWEEN '1/1/2003' AND '12/31/2003'

SELECT BusinessEntityID, HireDate
FROM HumanResources.Employee
WHERE BusinessEntityID BETWEEN 10 AND 15

SELECT BusinessEntityID, HireDate
FROM HumanResources.Employee
WHERE BusinessEntityID BETWEEN 15 AND 10

SELECT BusinessEntityID, HireDate
FROM HumanResources.Employee
WHERE BusinessEntityID NOT BETWEEN 10 AND 15

SET STATISTICS IO ON

SELECT FirstName, LastName
FROM Person.Person
WHERE LEFT(Lastname,1) ='M'

--Like
SELECT FirstName, LastName
FROM Person.Person
WHERE LastName LIKE 'M%'

--The second column in the index
SELECT FirstName, LastName
FROM Person.Person
WHERE FirstName = 'Zheng'

--Nulls
--19614 values
SELECT AddressLine1, AddressLine2
FROM Person.Address

--4 values
SELECT AddressLine1, AddressLine2
FROM Person.Address
WHERE AddressLine2 = '# 4'

--358 values
SELECT AddressLine1, AddressLine2
FROM Person.Address
WHERE AddressLine2 <> '# 4'

SELECT AddressLine1, AddressLine2
FROM Person.Address
WHERE AddressLine2 <> '# 4'
OR AddressLine2 IS NULL

SELECT AddressLine1, AddressLine2
FROM Person.Address
WHERE ISNULL(AddressLine2,'') <> '# 4'

--Multiple conditions
SELECT FirstName, LastName
FROM Person.Person
WHERE LastName = 'Adams' AND FirstName = 'Edward'

--Multiple conditions
SELECT FirstName, LastName
FROM Person.Person
WHERE LastName = 'Adams' OR FirstName = 'Edward'

/*Return a list of names the last name
must be Adams or Brown, the first name 
must be Edward
*/
SELECT FirstName, LastName
FROM Person.Person
WHERE LastName = 'Adams' OR LastName = 'Brown'
AND FirstName = 'Edward'

SELECT FirstName, LastName
FROM Person.Person
WHERE LastName = 'Adams' OR FirstName = 'Edward'
AND LastName = 'Brown'

SELECT FirstName, LastName
FROM Person.Person
WHERE (LastName = 'Adams' OR LastName = 'Brown')
AND FirstName = 'Edward'

SELECT FirstName, LastName
FROM Person.Person
WHERE LastName IN ('Adams','Brown')
AND FirstName = 'Edward'

Video 5 - JOINing Tables Part 1

Learn how to JOIN two or more tables in one query

Agenda

Why do we need to JOIN tables?
How to JOIN tables
Examples

Tools

SQL Server
If you have a SQL Server available, download the sample OLTP database, AdventureWorks, from www.Codeplex.com
Figure out which version of SQL server you are running and download that specific version
You can start with anything from 2008 to 2012 to follow along

Tools

SQL Server free virtual labs
http://www.microsoft.com/sqlserver/en/us/learning-center/virtual-labs.aspx
Go back to first video to learn more on how to do this

Why do we need to JOIN tables?

Why not have all the data in 1 gigantic table?
App wouldn't perform very well
Designed via a process called normalization
One table for each type of object
Key icon next to a field means that field is a primary key

How to JOIN Tables

SELECT col1, col2
FROM table1, table2
WHERE col1 = col2

SELECT col1, col2
FROM table1 INNER JOIN table2
ON col1 = col2

Column names of primary key and foreign key can be identical or not
Will explain reason why she doesn't like old syntax later

Demos, and More Demos!

/*
Requirements: Create a list of current employees,
their names, titles and departments
*/

SELECT * FROM HumanResources.Employee

--Join the Employee table to the Person.Person
--table to display the employee name with the title.
SELECT emp.BusinessEntityID AS EmployeeID, contact.FirstName,
contact.Lastname, emp.JobTitle
FROM HumanResources.Employee AS emp
INNER JOIN Person.Person AS contact
ON emp.BusinessEntityID = contact.BusinessEntityID
ORDER BY emp.BusinessEntityID

SELECT emp.BusinessEntityID AS EmployeeID, contact.FirstName,
contact.Lastname, emp.JobTitle,
edh.DepartmentID, edh.StartDate, edh.EndDate
FROM HumanResources.Employee AS emp
INNER JOIN Person.Person AS contact
ON emp.BusinessEntityID = contact.BusinessEntityID
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON emp.BusinessEntityID = edh.BusinessEntityID
WHERE edh.EndDate IS NULL

SELECT emp.BusinessEntityID AS EmployeeID, contact.FirstName,
contact.Lastname, emp.JobTitle,
dept.DepartmentID, dept.Name
FROM HumanResources.Employee AS emp
INNER JOIN Person.Person AS contact
ON emp.BusinessEntityID = contact.BusinessEntityID
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON emp.BusinessEntityID = edh.BusinessEntityID
INNER JOIN HumanResources.Department AS dept
ON edh.DepartmentID = dept.DepartmentID
WHERE edh.EndDate IS NULL
ORDER BY emp.BusinessEntityID

--The "other" way
SELECT emp.BusinessEntityID AS EmployeeID, contact.FirstName,
contact.LastName, emp.JobTitle,
dept.DepartmentID, dept.Name
FROM HumanResources.Employee AS emp,
Person.Person AS contact,
HumanResources.EmployeeDepartmentHistory AS edh,
HumanResources.Department AS dept
WHERE edh.EndDate IS NULL
AND emp.BusinessEntityID = contact.BusinessEntityID
AND emp.BusinessEntityID = edh.BusinessEntityID
AND edh.DepartmentID = dept.DepartmentID
ORDER BY emp.BusinessEntityID

--What if I forget one of the join condtions?
SELECT emp.BusinessEntityID AS EmployeeID, contact.FirstName,
contact.LastName, emp.JobTitle,
dept.DepartmentID,dept.Name
FROM HumanResources.Employee AS emp,
Person.Person AS contact,
HumanResources.EmployeeDepartmentHistory AS edh,
HumanResources.Department AS dept
WHERE edh.EndDate IS NULL
AND emp.BusinessEntityID = contact.BusinessEntityID
AND edh.DepartmentID = dept.DepartmentID
ORDER BY emp.BusinessEntityID

Video 6 - JOINing Tables Part 2

Agenda

Tools
OUTER JOIN
Sub-Query
FULL OUTER JOIN
CROSS JOIN

Tools

SQL Server
If you have a SQL Server available, download the sample OLTP database, AdventureWorks, from www.Codeplex.com
Figure out which version of SQL server you are running and download that specific version
You can start with anything from 2008 to 2012 to follow along

Tools

SQL Server free virtual labs
http://www.microsoft.com/sqlserver/en/us/learning-center/virtual-labs.aspx
Go back to first video to learn more on how to do this

Demos, and More Demos!

--The list of customers with the sales order ID
SELECT c.CustomerID, c.AccountNumber, o.SalesOrderID
FROM Sales.Customer AS c
INNER JOIN Sales.SalesOrderHeader AS o
ON c.CustomerID = o.CustomerID

/*
Display all the customers with orders even
if no orders yet
Using LEFT OUTER JOIN
*/
SELECT c.CustomerID, c.AccountNumber, o.SalesOrderID
FROM Sales.Customer AS c
LEFT OUTER JOIN Sales.SalesOrderHeader AS o
ON c.CustomerID = o.CustomerID

--Using LEFT OUTER JOIN
SELECT c.CustomerID, c.AccountNumber, o.SalesOrderID
FROM Sales.SalesOrderHeader AS o
RIGHT OUTER JOIN Sales.Customer AS c
ON c.CustomerID = o.CustomerID

--Use a sub-query to find the customers
--Who have placed an order
SELECT CustomerID, AccountNumber
FROM Sales.Customer
WHERE CustomerID IN (SELECT CustomerID FROM Sales.SalesOrderHeader)

/*
Number of orders: 31465
Number of customers: 19820 [NOTE: run the first two lines of code above to get this number]
Number of customers with orders: 19119
*/

--Finding the customers who haven't
--Placed an order
SELECT c.CustomerID, c.AccountNumber
FROM Sales.Customer AS c
LEFT OUTER JOIN Sales.SalesOrderHeader AS o
ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL
ORDER BY c.CustomerID

--Step 2 get all rows from the left side
SELECT c.CustomerID, c.AccountNumber, o.SalesOrderID
FROM Sales.Customer AS c
LEFT OUTER JOIN Sales.SalesOrderHeader o
ON c.CustomerID = o.CustomerID
ORDER BY c.CustomerID

--Step 3 filter out rows that are a match
SELECT c.CustomerID, c.AccountNumber, o.SalesOrderID
FROM Sales.Customer AS c
LEFT OUTER JOIN Sales.SalesOrderHeader o
ON c.CustomerID = o.CustomerID
WHERE o.SalesOrderID IS NULL
ORDER BY c.CustomerID

--Use a sub-query
SELECT CustomerID, AccountNumber
FROM Sales.Customer
WHERE CustomerID NOT IN
(SELECT CustomerID FROM Sales.SalesOrderHeader)

/*
FULL OUTER JOIN
Hard to find a reason to do this in a properly 
designed database, so we'll create a table that doesn't belong
*/
IF OBJECT_ID('DepartmentValue') IS NOT NUL
DROP TABLE DepartmentValue

CREATE TABLE DepartmentValue(DepartmentID int,SomeValue varchar(50))
INSERT INTO DepartmentValue (DepartmentID, SomeValue)
VALUES (1, 'one'), (2, 'two'), (20, 'twenty')

SELECT * FROM DepartmentValue

--Do an INNER JOIN
SELECT d.DepartmentID, d.Name, dv.SomeValue
FROM HumanResources.Department AS d
INNER JOIN DepartmentValue AS dv
ON d.DepartmentID = dv.DepartmentID

--Do an LEFT OUTER JOIN
SELECT d.DepartmentID, d.Name, dv.SomeValue
FROM HumanResources.Department AS d
LEFT OUTER JOIN DepartmentValue AS dv
ON d.DepartmentID = dv.DepartmentID

--A RIGHT OUTER JOIN
SELECT d.DepartmentID, d.Name, dv.SomeValue, dv.DepartmentID
FROM HumanResources.Department AS d
RIGHT OUTER JOIN DepartmentValue AS dv
ON d.DepartmentID = dv.DepartmentID

--FULL OUTER JOIN
SELECT d.DepartmentID, d.Name, dv.SomeValue, dv.DepartmentID
FROM HumanResources.Department AS d
FULL OUTER JOIN DepartmentValue AS dv
ON d.DepartmentID = dv.DepartmentID

--Using CROSS JOIN to join two tables with no relationship
--This is a Cartesian Product!

--First, how many rows in the Location and Product tables?
SELECT * FROM Production.Location --14
SELECT * FROM Production.Product --504

--This join is like multiplying the tables together
SELECT l.LocationID, l.Name, p.ProductID, p.Name as ProductName
FROM Production.Product AS p
CROSS JOIN Prodution.Location AS l

SELECT 14 * 504

Video 7 - Aggregate Queries

Agenda

Tools
Common Aggregate Functions
Syntax
Examples

Tools

SQL Server
If you have a SQL Server available, download the sample OLTP database, AdventureWorks, from www.Codeplex.com
Figure out which version of SQL server you are running and download that specific version
You can start with anything from 2008 to 2012 to follow along

Tools

SQL Server free virtual labs
http://www.microsoft.com/sqlserver/en/us/learning-center/virtual-labs.aspx
Go back to first video to learn more on how to do this

Common Aggregate Functions

COUNT
MIN
MAX
SUM
AVG

Used to summarize our data or group data into sections

Syntax

SELECT FUNCTION(col1)
FROM table1
SELECT FUNCTION(col1),col2
FROM table1
GROUP BY col2

Demos, and More Demos!

--Find the count of the rows
SELECT COUNT(*) AS "Cost of Sales"
FROM Sales.SalesOrderHeader

--Look at subTotal
SELECT COUNT(SubTotal) AS "Count of SubTotal",
SUM(SubTotal) AS "Sum of SubTotal",
MIN(SubTotal) AS "Min SubTotal",
MAX(SubTotal) AS "Max SubTotal",
AVG(SubTotal) AS "Avg SubTotal"
FROM Sales.SalesOrderHeader

--Use an expression
SELECT SUM(SubTotal + TaxAmt + Freight) AS "Total Due"
FROM Sales.SalesOrderHeader

--Nulls?
SELECT COUNT(*) AS "Cost of rows",
    COUNT(SubTotal) AS "Count of SubTotal",
    COUNT(CurrencyRateID) AS "Count of CurrencyRateID"
FROM Sales.SalesOrderHeader

SELECT AVG(CurrencyRateID) AS "Avg CurrencyRateID",
SUM(CurrencyRateID)/COUNT(*) AS "Sum/Count of Rows"
FROM Sales.SalesOrderHeader

--Display the count of orders for each customer
SELECT CustomerID, COUNT(*) AS "Order Count"
FROM Sales.SalesOrderHeader

--Add a GROUP BY clause
SELECT CustomerID, COUNT(*) AS "Order Count"
FROM Sales.SalesOrderHeader
GROUP BY CustomerID

--Order by the expression
SELECT CustomerID, COUNT(*) AS "Order Count"
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY COUNT() DESC

--Get some more details about customer
SELECT o.CustomerID, COUNT(*) AS "Order Count",
c.AccountNumber
FROM Sales.SalesOrderHeader AS o
INNER JOIN Sales.Customer AS c
ON o.CustomerID = c.CustomerID
GROUP BY o.CustomerID, c.AccountNumber

/* Any column that appears in the SELECT list or
ORDER BY clause that is not part of an aggregate
function MUST appear in the GROUP BY clause
*/
SELECT o.CustomerID, COUNT(*) AS "Order Count"
FROM Sales.SalesOrderHeader AS o
INNER JOIN Sales.Customer AS c
ON o.CustomerID = c.CustomerID
GROUP BY o.CustomerID, c.AccountNumber
ORDER BY c.AccountNumber

--Don't include anything you don't need
SELECT o.CustomerID, COUNT(*) AS "Order Count"
FROM Sales.SalesOrderHeader AS o
INNER JOIN Sales.Customer AS c
ON o.CustomerID = c.CustomerID
GROUP BY o.CustomerID, c.AccountNumber, o.SalesOrderID
ORDER BY c.AccountNumber

--Don't use an aggregate function in WHERE
SELECT o.CustomerID, COUNT(*) AS "Order Count"
FROM Sales.SalesOrderHeader AS o
INNER JOIN Sales.Customer AS c
ON o.CustomerID = c.CustomerID
WHERE COUNT(*) > 5
GROUP BY o.CustomerID

--Use HAVING
SELECT o.CustomerID, COUNT(*) AS "Order Count"
FROM Sales.SalesOrderHeader AS o
INNER JOIN Sales.Customer AS c
ON o.CustomerID = c.CustomerID
GROUP BY o.CustomerID
HAVING COUNT(*) > 5

SELECT o.CustomerID, COUNT(*) AS "Order Count"
FROM Sales.SalesOrderHeader AS o
INNER JOIN Sales.Customer AS c
ON o.CustomerID = c.CustomerID
GROUP BY o.CustomerID
HAVING SUM(SubTotal) > 10000

--Compare to this
SELECT o.CustomerID, COUNT(*) AS "Order Count"
FROM Sales.SalesOrderHeader AS o
INNER JOIN Sales.Customer AS c
ON o.CustomerID = c.CustomerID
WHERE SubTotal > 10000
GROUP BY o.CustomerID

--Let's get a count of orders by customer by year
SELECT c.CustomerID, COUNT(SalesOrderID) AS CountOfSales,
YEAR(o.OrderDate) AS OrderYear
FROM Sales.SalesOrderHeader AS o
INNER JOIN Sales.Customer AS c
ON o.CustomerID = c.CustomerID
GROUP BY c.CustomerID

--Let's get a count of orders by customer by year
SELECT c.CustomerID, COUNT(SalesOrderID) AS CountOfSales,
YEAR(o.OrderDate) AS OrderYear
FROM Sales.SalesOrderHeader AS o
INNER JOIN Sales.Customer AS c
ON o.CustomerID = c.CustomerID
GROUP BY c.CustomerID, o.OrderDate
ORDER BY c.CustomerID, YEAR(o.OrderDate)

--Add complete expression to GROUP BY
SELECT c.CustomerID, COUNT(SalesOrderID) AS CountOfSales,
YEAR(o.OrderDate) AS OrderYear
FROM Sales.SalesOrderHeader AS o
INNER JOIN Sales.Customer AS c
ON o.CustomerID = c.CustomerID
GROUP BY c.CustomerID, YEAR(o.OrderDate)
ORDER BY c.CustomerID, YEAR(o.OrderDate)

--Aggregate of character data
SELECT MIN(LastName) AS "Min last name",
MAX(LastName) AS "Max last name"
FROM Person.Person

SELECT AVG(LastName)
FROM Person.Person