module 10 summarizing data
TRANSCRIPT
-
8/9/2019 Module 10 Summarizing Data
1/32
Module 10:Summarizing Data
Vidya Vrat Agarwal. | MCT, MCSD
-
8/9/2019 Module 10 Summarizing Data
2/32
Overview
Using Aggregate Functions
GROUP BY Fundamentals
Generating Aggregate Values Within Result Sets
Using the COMPUTE and COMPUTE BY Clauses
Listing the TOP n Values
-
8/9/2019 Module 10 Summarizing Data
3/32
Aggregate function
Aggregate functionAggregate function Description
DescriptionDescription
AVG
AVG Average of values in a numeric expression
Average of values in a numeric expression
COUNT
COUNT Number of values in an expression
Number of values in an expression
COUNT (*)
COUNT (*) Number of selected rows
Number of selected rows
MAX
MAX Highest value in the expression
Highest value in the expression
MIN
MIN Lowest value in the expression
Lowest value in the expression
SUM
SUM Total values in a numeric expression
Total values in a numeric expression
Using Aggregate Functions
A functions that performs a calculation on a column in a
set of rows and returns a single value.
-
8/9/2019 Module 10 Summarizing Data
4/32
Using Aggregate Functions with Null Values
Most Aggregate Functions Ignore Null Values
COUNT(*) Function Counts Rows with Null Values
USE northwind
SELECT COUNT (*)
FROM employees
Aggregate Query
A query (SQL statement) that summarizes informationfrom multiple rows by including an aggregate function
such as Sum or Avg .
-
8/9/2019 Module 10 Summarizing Data
5/32
GROUP BY Fundamentals
Using the GROUP BY Clause
Using the GROUP BY Clause with the HAVING Clause
Divides a table into groups.
-
8/9/2019 Module 10 Summarizing Data
6/32
Using the GROUP BY Clause
USE northwind
SELECT productid, orderid, quantity
FROM [order details]
USE northwind
SELECT productid, SUM(quantity)
AS total_quantity
FROM [order details]
GROUP BY productid
productid
productidproductid total_quantity
total_quantitytotal_quantity
1
1 15
15
2
2 35
35
3
3 45
45
productid
productidproductid orderid
orderidorderid quantity
quantityquantity
1
1 1
1 5
5
1
1 1
1 10
10
2
2 1
1 10
10
2
2 2
2 25
25
3
3 1
1 15
15
3
3 2
2 30
30
productid
productidproductid total_quantity
total_quantitytotal_quantity
2
2 35
35Only rows thatsatisfy the WHERE
clause are groupedUSE northwind
SELECT productid, SUM(quantity)
AS total_quantity
FROM [order details]
WHERE productid = 2
GROUP BY productid
WHERE productid = 2
-
8/9/2019 Module 10 Summarizing Data
7/32
Using the GROUP BY Clause with the HAVING Clause
USE northwind
SELECT productid, SUM(quantity)
AS total_quantity
FROM [order details]
GROUP BY productid
HAVING SUM(quantity) >=30
productidproductidproductid total_quantitytotal_quantitytotal_quantity
22 3535
33 4545
productidproductidproductid orderidorderidorderid quantityquantityquantity
11 11 55
11 11 1010
22 11 1010
22 22 2525
33 11 1515
33 22 3030
Server: Msg 147, Level 15, State 1, Line 5
An aggregate may not appear in the WHERE clause
-
8/9/2019 Module 10 Summarizing Data
8/32
Generating Aggregate Values Within Result Sets
Using the GROUP BY Clause with the ROLLUP Operator
Using the GROUP BY Clause with the CUBE Operator
-
8/9/2019 Module 10 Summarizing Data
9/32
USE northwind
SELECT productid, orderid, SUM(quantity) AS total_quantity
FROM [order details]
GROUP BY productid, orderid
WITH ROLLUP
ORDER BY productid, orderid
USE northwind
SELECT productid, orderid, SUM(quantity) AS total_quantity
FROM [order details]
GROUP BY productid, orderid
WITH ROLLUP
ORDER BY productid, orderid
productidproductidproductid orderidorderidorderid total_quantitytotal_quantitytotal_quantity
NULLNULL NULLNULL 9595
11 NULLNULL 1515
11 11 55
11 22 1010
22 NULLNULL 353522 11 1010
22 22 2525
33 NULLNULL 4545
33 11 1515
33 22 3030
DescriptionDescription
Grand totalGrand total
Summarizes only rows for productid 1Summarizes only rows for productid 1
Detail value forproductid 1, orderid 1Detail value forproductid 1, orderid 1
Detail value forproductid 1, orderid 2Detail value forproductid 1, orderid 2
Summarizes only rows for productid 2Summarizes only rows for productid 2
Detail value forproductid 2, orderid 1Detail value forproductid 2, orderid 1
Detail value forproductid 2, orderid 2Detail value forproductid 2, orderid 2
Summarizes only rows for productid 3Summarizes only rows for productid 3
Detail value forproductid 3, orderid 1Detail value forproductid 3, orderid 1
Detail value forproductid 3, orderid 2Detail value forproductid 3, orderid 2
Using the GROUP BY Clause with the ROLLUP Operator
-
8/9/2019 Module 10 Summarizing Data
10/32
Using the GROUP BY Clause with the CUBE Operator
USE northwind
SELECT productid, orderid, SUM(quantity) AS total_quantity
FROM [order details]
GROUP BY productid, orderid
WITH CUBE
ORDER BY productid, orderid
USE northwindSELECT productid, orderid, SUM(quantity) AS total_quantity
FROM [order details]
GROUP BY productid, orderid
WITH CUBE
ORDER BY productid, orderid
productidproductidproductid
orderidorderidorderid
total_quantitytotal_quantitytotal_quantity
NULLNULL NULLNULL 9595
NULLNULL 11 3030
NULLNULL 22 6565
11 NULLNULL 1515
11 11 55
11 22 1010
22 NULLNULL 3535
22 11 101022 22 2525
33 NULLNULL 4545
33 11 1515
33 22 3030
DescriptionDescription
Grand totalGrand total
Summarizes all rows for orderid 1Summarizes all rows for orderid 1
Summarizes all rows for orderid 2Summarizes all rows for orderid 2
Summarizes only rows for productid 1Summarizes only rows for productid 1
Detail value forproductid 1, orderid 1Detail value forproductid 1, orderid 1
Detail value forproductid 1, orderid 2Detail value forproductid 1, orderid 2
Summarizes only rows for productid 2Summarizes only rows for productid 2
Detail value forproductid 2, orderid 1Detail value forproductid 2, orderid 1Detail value forproductid 2, orderid 2Detail value forproductid 2, orderid 2
Summarizes only rows for productid 3Summarizes only rows for productid 3
Detail value forproductid 3, orderid 1Detail value forproductid 3, orderid 1
Detail value forproductid 3, orderid 2Detail value forproductid 3, orderid 2
The CUBE operator
produces two
more summary
values than the
ROLLUP operator
The CUBE operator
produces two
more summary
values than the
ROLLUP operator
-
8/9/2019 Module 10 Summarizing Data
11/32
Using the COMPUTE and COMPUTE BY Clauses
USE northwind
SELECT productid, orderid, quantity
FROM [order details]
ORDER BY productid, orderid
COMPUTE SUM(quantity) BY productid
COMPUTE SUM(quantity)
USE northwind
SELECT productid, orderid, quantity
FROM [order details]
ORDER BY productid, orderid
COMPUTE SUM(quantity) BY productid
COMPUTE SUM(quantity)
USE northwind
SELECT productid, orderid, quantity
FROM [order details]
ORDER BY productid, orderid
COMPUTE SUM(quantity)
USE northwind
SELECT productid, orderid, quantity
FROM [order details]
ORDER BY productid, orderid
COMPUTE SUM(quantity)
COMPUTECOMPUTE COMPUTE BYCOMPUTE BY
productidproductidproductid orderidorderidorderid quantityquantityquantity
11 11 55
11 22 1010
22 11 1010
22 22 2525
33 11 151533 22 3030
sum 95sum 95
productidproductidproductid orderidorderidorderid quantityquantityquantity
11 11 55
11 22 1010
sum 15sum 15
22 11 1010
22 22 2525sum 35sum 35
33 11 1515
33 22 3030
sum 45sum 45
sum 95sum 95
-
8/9/2019 Module 10 Summarizing Data
12/32
Listing the TOP n Values
Lists Only the First n Rows of a Result Set
Specifies the Range of Values in the ORDER BY Clause
Returns Ties if WITH TIES Is Used
USE northwind
SELECT TOP 5 orderid, productid, quantity
FROM [order details]
ORDER BY quantity DESC
USE northwind
SELECT TOP 5 orderid, productid, quantity
FROM [order details]
ORDER BY quantity DESC
USE northwind
SELECT TOP 5 WITH TIES orderid, productid, quantity
FROM [order details]
ORDER BY quantity DESC
USE northwind
SELECT TOP 5 WITH TIES orderid, productid, quantity
FROM [order details]
ORDER BY quantity DESC
-
8/9/2019 Module 10 Summarizing Data
13/32
Recommended Practices
Use the ORDER BY Clause to Guarantee a Sort OrderUse the ORDER BY Clause to Guarantee a Sort Order
Avoid Using the COMPUTE or COMPUTE BY ClauseAvoid Using the COMPUTE or COMPUTE BY Clause
Avoid Using Aggregate Functions with Null ValuesAvoid Using Aggregate Functions with Null Values
Use the ROLLUP Operator Whenever PossibleUse the ROLLUP Operator Whenever Possible
Index Frequently Aggregated ColumnsIndex Frequently Aggregated Columns
-
8/9/2019 Module 10 Summarizing Data
14/32
Check Your Understanding.
-
8/9/2019 Module 10 Summarizing Data
15/32
Q.1 What is an Aggregate Function.? Give names of thefunctions.
-
8/9/2019 Module 10 Summarizing Data
16/32
Q.2. What is an Aggregate Query. ?
-
8/9/2019 Module 10 Summarizing Data
17/32
Q.3. What is Group By.?
-
8/9/2019 Module 10 Summarizing Data
18/32
Q.4. What is the difference between Group By and GroupBy with Having.?
-
8/9/2019 Module 10 Summarizing Data
19/32
Q.5. All Aggregate Functions Ignore Null Values.
1. True
2. False
-
8/9/2019 Module 10 Summarizing Data
20/32
Q.6. COUNT(*) Function Counts Rows withNull Values.
1. True2. False
-
8/9/2019 Module 10 Summarizing Data
21/32
Q.7. CUBE operator produces more rows of summaryvalues than ROLLUP operator.
1. True
2. False
-
8/9/2019 Module 10 Summarizing Data
22/32
Q.8. What is the difference between COMPUTE andCOMPUTE BY.?
-
8/9/2019 Module 10 Summarizing Data
23/32
Q.9 Consider the following table named Salary.
500Null3500Samuel
100020004000AbrahamNullNull200Ravi
Null10002500Joseph
Null20005000Mathews
50015004500AlexisDEDUCTIONSHRABASICNAME
What will be the result of
Select COUNT(*) From Salary
a. 5 b. 6 c. 4 d. 3
-
8/9/2019 Module 10 Summarizing Data
24/32
Q.10. Consider the following table named Salary.
500Null3500Samuel
100020004000Abraham
NullNull200Ravi
Null10002500Joseph
Null20005000Mathews
50015004500Alexis
DEDUCTIONSHRABASICNAME
What will be the result of
Select COUNT(Basic) From Salary
a. 5 b. 6 c. 4 d. 3
-
8/9/2019 Module 10 Summarizing Data
25/32
Q.11. Consider the following table named Salary.
500Null3500Samuel
100020004000Abraham
NullNull200Ravi
Null10002500Joseph
Null20005000Mathews
50015004500Alexis
DEDUCTIONSHRABASICNAME
What will be the result of
Select AVG(Hra) From Salary
a. 1083.33 b.1625.00
c. 1803.33 d. None of the above
-
8/9/2019 Module 10 Summarizing Data
26/32
Q.12 What will COUNT(*) return if there are no rows in thetable matching the search criteria.
a. -1
b. Null
c. 0
d. None of the above
-
8/9/2019 Module 10 Summarizing Data
27/32
Q.13 SQL provides Six Aggregate Functions.
1. True
2. False
-
8/9/2019 Module 10 Summarizing Data
28/32
Q.14 Keyword Distinct can be used with Count(*).
1. Yes
2. No
-
8/9/2019 Module 10 Summarizing Data
29/32
Q.15. Consider the following table named Salary
500Null3500Samuel
100020004000Abraham
NullNull200Ravi
Null10002500Joseph
Null20005000Mathews
50015004500AlexisDEDUCTIONSHRABASICNAME
What will be the result of
Select MIN(Deductions) From Salary
a. 1000 b.333.33 c. 500 d.None
-
8/9/2019 Module 10 Summarizing Data
30/32
Q.16. Consider the following table named Salary
500NullD1Samuel
10002000D3Abraham
NullNullD2Ravi
Null1000D3Joseph
Null2000D1Mathews
5001500D1AlexisDEDUCTIONSHRADEPTNAME
What will be the result of
Select AVG(HRA) From Salary where DEPT = D1
a. 1166.67 b.1750 c. 3500 d.None
-
8/9/2019 Module 10 Summarizing Data
31/32
Review
Using Aggregate Functions
GROUP BY Fundamentals
Generating Aggregate Values Within Result Sets
Using the COMPUTE and COMPUTE BY Clauses
Listing the TOP n Values
-
8/9/2019 Module 10 Summarizing Data
32/32
Nothing great is everachieved without enthusiasm
Thank You.