04 - grouping and aggregating data
DESCRIPTION
The concepts and procedures for grouping and aggregating data.TRANSCRIPT
-
04 | Grouping and Aggregating Data
-
Querying Microsoft SQL Server 2012 Jump Start
01 | Introducing SQL Server 2012
SQL Server types of statements; other SQL statement elements; basic SELECT statements
02 | Advanced SELECT StatementsDISTINCT, Aliases, scalar functions and CASE, using JOIN and MERGE; Filtering and sorting data, NULL
values
03 | SQL Server Data Types Introduce data types, data type usage, converting data types, understanding SQL Server function types
04 | Grouping and Aggregating DataAggregate functions, GROUP BY and HAVING clauses, subqueries; self-contained, correlated, and EXISTS; Views, inline-table
valued functions, and derived tables
| Lunch Break Eat, drink, and recharge for the afternoon session
-
STDEV STDEVP VAR VARP
SUM MIN MAX AVG COUNT COUNT_BIG
CHECKSUM_AGG GROUPING GROUPING_ID
Common Statistical Other
-
UniqueOrders Avg_UnitPrice Min_OrderQty Max_LineTotal------------- ------------ ------------ -------------31465 465.0934 1 27893.619000
SELECT COUNT (DISTINCT SalesOrderID) ASUniqueOrders, AVG(UnitPrice) AS Avg_UnitPrice, MIN(OrderQty)AS Min_OrderQty, MAX(LineTotal) AS Max_LineTotalFROM Sales.SalesOrderDetail;
-
SELECT SalesPersonID, YEAR(OrderDate) AS OrderYear,COUNT(CustomerID) AS All_Custs,COUNT(DISTINCT CustomerID) AS Unique_CustsFROM Sales.SalesOrderHeaderGROUP BY SalesPersonID, YEAR(OrderDate);
SalesPersonID OrderYear All_Custs Unique_custs----------- ----------- ----------- ------------289 2006 84 48281 2008 52 27285 2007 9 8277 2006 140 57
-
SELECT FROM WHERE GROUP BY ;
SELECT SalesPersonID, COUNT(*) AS CntFROM Sales.SalesOrderHeaderGROUP BY SalesPersonID;
-
Logical Order Phase Comments
5 SELECT
1 FROM
2 WHERE
3 GROUP BY Creates groups
4 HAVING Operates on groups
6 ORDER BY
-
SELECT productid, MAX(OrderQty) AS largest_orderFROM Sales.SalesOrderDetailGROUP BY productid;
SELECT CustomerID, COUNT(*) AS cntFROM Sales.SalesOrderHeaderGROUP BY CustomerID;
-
SELECT CustomerID, COUNT(*) ASCount_OrdersFROM Sales.SalesOrderHeaderGROUP BY CustomerIDHAVING COUNT(*) > 10;
-
Using a COUNT(*) expression in HAVING clause is useful to solve common business problems:
Show only customers that have placed more than one order:
Show only products that appear on 10 or more orders:
SELECT Cust.Customerid, COUNT(*) AS cntFROM Sales.Customer AS CustJOIN Sales.SalesOrderHeader AS Ord ON Cust.CustomerID = ORD.CustomerIDGROUP BY Cust.CustomerIDHAVING COUNT(*) > 1;
SELECT Prod.ProductID, COUNT(*) AS cntFROM Production.Product AS ProdJOIN Sales.SalesOrderDetail AS Ord ON Prod.ProductID = Ord.ProductIDGROUP BY Prod.ProductIDHAVING COUNT(*) >= 10;
-
Working with subqueries
-
Writing scalar subqueries
SELECT SalesOrderID, ProductID, UnitPrice, OrderQtyFROM Sales.SalesOrderDetailWHERE SalesOrderID = (SELECT MAX(SalesOrderID) AS LastOrderFROM Sales.SalesOrderHeader);
-
Writing multi-valued subqueries
SELECT CustomerID, SalesOrderId,TerritoryIDFROM Sales.SalesorderHeaderWHERE CustomerID IN (SELECT CustomerIDFROM Sales.CustomerWHERE TerritoryID = 10);
-
Writing queries using EXISTS with subqueries
SELECT CustomerID, PersonIDFROM Sales.Customer AS CustWHERE EXISTS (SELECT * FROM Sales.SalesOrderHeader AS OrdWHERE Cust.CustomerID = Ord.CustomerID);
SELECT CustomerID, PersonIDFROM Sales.Customer AS CustWHERE NOT EXISTS (SELECT * FROM Sales.SalesOrderHeader AS OrdWHERE Cust.CustomerID = Ord.CustomerID);
-
CREATE VIEW HumanResources.EmployeeListASSELECT BusinessEntityID, JobTitle, HireDate,VacationHoursFROM HumanResources.Employee;
SELECT * FROM HumanResources.EmployeeList
-
CREATE FUNCTION Sales.fn_LineTotal (@SalesOrderID INT)RETURNS TABLEASRETURN
SELECT SalesOrderID,CAST((OrderQty * UnitPrice * (1 - SpecialOfferID))AS DECIMAL(8, 2)) AS LineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderID = @SalesOrderID ;
-
Writing queries with derived tables
SELECT FROM (
) AS ;
-
Derived Tables Must
Have an alias Have names for all columns
Have unique names for all columns
Not use an ORDER BY clause (without TOP or OFFSET/FETCH)
Not be referred to multiple times in the same query
Derived Tables May
Use internal or external aliases for columns
Refer to parameters and/or variables
Be nested within other derived tables
-
DECLARE @emp_id INT = 9;SELECT orderyear, COUNT(DISTINCT custid) AS cust_countFROM (
SELECT YEAR(orderdate) AS orderyear, custidFROM Sales.OrdersWHERE empid=@emp_id
) AS derived_yearGROUP BY orderyear;
-
WITH CTE_year AS(SELECT YEAR(OrderDate) AS OrderYear, customerIDFROM Sales.SalesOrderHeader)SELECT orderyear, COUNT(DISTINCT CustomerID) AS CustCountFROM CTE_yearGROUP BY OrderYear;
-
STDEV STDEVP VAR VARP
SUM MIN MAX AVG COUNT COUNT_BIG
CHECKSUM_AGG GROUPING GROUPING_ID
Common Statistical Other
-
Views are named tables expressions with definitions stored in a database
that can be referenced in a SELECT statement just like a table
Views are defined with a single SELECT statement and then saved in the
database as queries
Table-valued functions are created with the CREATE FUNCTION. They
contain a RETURN type of table
Derived tables allow you to write more modular queries
as named query expressions that are created within an outer SELECT
statement. They represent a virtual relational table so are not stored in
the database
CTEs are similar to derived tables in scope and naming requirements but
unlike derived tables, CTEs support multiple definitions, multiple
references, and recursion
-
Querying Microsoft SQL Server 2012 Jump Start
01 | Introducing SQL Server 2012
SQL Server types of statements; other SQL statement elements; basic SELECT statements
02 | Advanced SELECT StatementsDISTINCT, Aliases, scalar functions and CASE, using JOIN and MERGE; Filtering and sorting data, NULL
values
03 | SQL Server Data Types Introduce data types, data type usage, converting data types, understanding SQL Server function types
04 | Grouping and Aggregating dataAggregate functions, GROUP BY and HAVING clauses, subqueries; self-contained, correlated, and EXISTS; Views, inline-table
valued functions, and derived tables
| Lunch BreakEat, drink, and recharge for the afternoon session
-
2013 Microsoft Corporation. All rights reserved. Microsoft, Windows, Office, Azure, System Center, Dynamics and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.