Lesson: Aggregating Data
�
66
Lesson Overview
This lesson covers ho"'' you can perform certain calculations on a database table column across multiple rows of the database table.
Lesson Objectives
After completing thjs lesson, you will be able to:
• Determine aggregated values on table cohunns using a single SELECT
statement.
• List the aggregate functions supported by HANA.
• Determine such aggregated values for groups of rows, using the GROUP
BY clause.
• Filter such groups using the HAVING clause.
Business Example
Calculations across multiple rows
Figure 144: Aggregate ExpressionsCount ( *)
+ You can calculate the number of rows in the result set using COUNT. + Rows containing only NULL values are included.
;;. What Is the quantity of cars with the brand "Audi"?
SELECT COUNT(*) FROM Ca.r
WHERE Brand = 1 Audi 1 ; Figure 145: Aggregate Expressions
© 2014 SAP AG or an SAP affiliate company. All rights reserved.
COUNT(*)
4
HA150 Lesson: Aggregating Data
Count(*)
You can explicitly name columns created by aggregate expressions.
>What is the quantity of cars with the brand "Audi"?
SELECT COUNT(*) AS "Quantity of Audi" FROM Car
WHERE Brand= 'Audi';
Quantity of Audi
4
Figure 146: Aggregate Expressions
Count (<column>)
• You can calculate the number of values within a sing le column.
• NULL values are not included.
• You can only use a single column as parameter of COUNT.
> How many cars are registered to an owner?
;;. This does not calculate the number of different owners!
SELECT COUNT(Owner) FROM Car;
Figure 147: Aggregate Expressions
COUNT (OWNER)
Unit 3: Aggregating Data HA150
�
68
Count (DISTINCT <co1umn>)
• You can calculate the number of different NOT-NULL-values of a certain
column.
• You can use only a sing l e column as parameter for COUNT DISTINCT.
NULL values are not included.
'!> How many different owners have a registered car?
SELECT COUNT(DISTINCT Owner) FROM Car;
Figure 148: Aggregate Expressions
SELECT * FROM T; SELECT S FROM T; SELECT COUNT ( *) FROM T; SELECT COUNT ( S) FROM T; FROM"T;" SELECT COUNT(DISTINCT S) FROM T;
Figure 149: Aggregate Expressions
? ? ? x x CO T(') COUNT(S)
COUNT (DISTINCT OWNER) 8
...
1
MIN/MAX(<co1wnn>)
You can calculate the minimum or maximum value in a column.
>-What is the horsepower range of the registered cars?
SELECT MIN(HP), MAX(HP) FROM Car;
Figure 150: Aggregate Expressions
© 2014 SAP AG or an SAP affiliate company.
All rights reserved.
MIN (HP) MAX (HP)
75 260
HA150 Lesson: Aggregating Data
You can combine aggregate expressions and "normal" functions.
>In which year was the youngest owner born?
SELECT M AX (YEAR (Birthday) ) AS Year
FROM Owner.; ... .
SELECT YEAR(MAX(Birthday)) AS Year FROM Owner;
Figure 151: Aggregate Expressions
The sequence of nested functions can be relevant.
>What is the lowest or highest HP value?
SELECT ABS (MAX (0 - HP) ) FROM Car;
SELECT MAX(ABS(O - HP)) FROM Car;
Figure 152: Aggregate Expressions
1986
ABS(MAX(O-HP)) 75
MAX (ABS (0-HP) )
260
The WHERE clause is included in the minimum and maximum calculation.
;;. What is the horsepower range of BMWs? SELECT MIN(HP), MAX(HP)
FROM Car
WHERE Brand= 'BMW';
Figure 153: Aggregate Expressions
MIN(HP) MAX(HP)
Unit 3: Aggregating Data HA150
70
• You can sum up the values in a column.
• The WHERE clause is included in the summation.
• NULL values in the column are ignored.
SUM (<column>)
• Individual NULL values contained in the column do not result in NULL for the
sum.
(as long as there is at least one numeric value)
> What is the sum of horsepower for all Audis?
SELECT SUM (HP) FROM Car
WHERE Brand = 1 Audi 1 ; Figure 154: Aggregate Expressions
SUM(HP)
744
SUM (DISTINCT <column>)
• You can sum up the distinct values of a column.
• The WHERE clause is included in the summation.
• NULL values of the column are ignored.
• Individual NULL values contained in the column do not result in NULL for the sum. (as long as there is at least one numeric value)
• If a value occurs multiple times, it is still counted only once when summing
SELECT SUM(DISTINCT HP) FROM Car
WHERE Brand= 'Audi'; Figure 155: Aggregate Expressions
© 2014 SAP AG or an SAP affiliate company. All rights reserved.
SUM(OISTINCT HP)
560
HA150 Lesson: Aggregating Data
AVG (<column>)
• You can calculate the average of all values in a column.
• The WHERE clause is included in the calculation.
• NULL values in the column are completely ignored
(in the numerator and denominator)
• Individual NULL values contained in the column do not result in NULL for the
average.
). What is the average horsepower of Audi?
SELECT AVG (HP)
FROM Car
WHERE Brand = 'Audi';
Figure 156: Aggregate Expressions
AVG(HP)
186
AVG (DISTINCT <column>)
• You can calculate the average of all distinct values in a column.
• The WHERE clause is included in the calculation. • NULL values in the column are completely ignored
(in the numerator and denominator)
• Individual NULL values contained in the column do not result in NULL for the
average.
• If a value occurs multiple times, it is still counted only once for the average
calculation.
SELECT AVG(DISTINCT HP)
FROM Car
WHERE Brand = 'Audi ' ;
Figure 157: Aggregate Expressions
AVG(DISTINCT HP)
186.6666666666666666666666666666667
Which aggregate expressions are available in SAP HANA?
�
Unit 3: Aggregating Data
72
SAP HANA provides the following aggregate expressions:
Name COUNT MIN MAX SUM AVG STD DEV VAR
Figure 159: Aggregate Expressions
Description Count Minimum Maximum Sum Average Standard Deviation Variance GROUP BY Figure 160: Group By
+ A table can be divided into (disjoint) groups of rows
+ A group is represented in the query result by a single row
+ Aggregate expressions will be evaluated separately for each group
,.. What is the number of cars per brand?
SELECT Brand, COUNT(*)
FROM Car
GROUP BY Brand;
Figure 161: Group By
© 2014 SAP AG or an SAP affiliate company.
All rights reserved.
BRAND - - - - Fiat vw BMW Mercedes Audi Skoda Renault Opel HA150 COUNT(*) - - - - 1 3 3 3 4 3 2 1 2014
HA150 Lesson: Aggregating Data
:;; What is the highest horse power value per brand?
SELECT Brand, MAX(HP)
FROM Car GROUP BY Brand; Figure 162: Group By BRAND MAX(HP) --- --- Fiat 75 vw 160 BMW 184 Mercedes 170 Audi 260 Skoda 136 Renault 136 Opel 120
NULL values of the GROUP BY column are treated as "normal" values creating a single group.
i>How often a certain overtime value occurs?
SELECT overtime, COUNT(*) AS Frequency FROM Official
GROUP BY overtime; OVERTIME FREQUENCY
Figure 163: Group By 10 3 20 1 -. ? 3 18 1 22 1
You can combine GROUP BY with ORDER BY for sorting.
SELECT Brand, MAX(HP) BRAND
---
FROM Car Audi
GROUP BY Brand BMW
ORDER BY 2 DESC, 1 ASC; Mercedes
vw Renault Skoda Opel Fiat Figure 164: Group By MAX(HP) --- 260 184 170 160 136 136 120 75
Unit 3: Aggregating Data HA150
74
You can use functions In the GROUP BY clause. +What is the number of owners per year of birth?
SELECT YEAR(Birthday}, COUNT(*) FROM Owner
GROUP BY YEAR(Birthday}
ORDER BY 2 DESC, 1 ASC; YEAR (BIRTHDAY) COUNT(*) --- --- ? 3 1986 3 1934 1 1952 1 1957 1 1966 1 Figure 165: Group By
The WHERE clause is processed before the grouping.
>What is the number of black cars per brand?
:i-Only brands with black cars are included into the result set.
SELECT Brand, COUNT(*) FROM Car
BRAND COUNT(*)
WHERE Color = 'black' GROUP BY Brand; Figure 166: Group By --- vw Mercedes BMW Skoda --- 3 1 1 1
You can use a combination of columns in the GROUP BY clause. SELECT Brand, Color, COUNT(*)
FROM Car
GROUP BY Brand, Color;
Figure 167: Group By
© 2014 SAP AG or an SAP affiliate company. All rights reserved.
BRAND --- Fiat vw BMH Mercedes t.fercedes Audi Audi Skoda Bl4l>1 BMfl Renaul.t Skoda Opel. Audi Audi COLOR COUNT(*) --- --- red 1 1>1ack 3 l>l.ue 1 white 2 l>l.ack 1 yella.1 1 blue 1 red 2 black 1 green 1 red 2 l>l.ack 1 green 1 orange 1 green 1 2014
HA150 Lesson: Aggregating Data
You can explicitly rename result columns In combination with GROUP BY.
SELECT Brand AS Manufacturer, Color, COUNT(*) AS "# of
cars'1 FROM GROUP BY Car Brand, Figure 168: Group By Color; HlWUl'.1iCTIJRER --- riat vw - Merced.ea Hercedea Audi Audi Skoda - - Renault COLOR --- red black blue Wbite bl a cl< yellow blue red black green red
What groups are included in the query result?
Figure 169: HAVING Clause
I of cars --- l. 3 l. 2 l. l. l. 2 l. l. 2
Using the HAVING clause you can specify which conditions a group must meet to be included in the result set.
;;.Which combinations of brand and color occur at least twice?
SELECT Brand, Color, COUNT(*) FROM Car
GROUP BY Brand, Color HAVING COUNT(*) >= 2;
Figure 170: HAVING
BRAND COLOR COUNT ( *)
VW black 3 Mercedes white 2 Skoda red 2
Unit 3: Aggregating Data HA150
76
The HAVING condition can reference columns not included in the projection list.
l>What is the number of brand-color combinations, where at least one car has less than 120 HP?
>We want to analyze based on the quantity of all cars, not only the cars with less than 120 HP.
SELECT Brand, Color, COUNT(*)
FROM Car
GROUP BY Brand, Color BRAND COLOR
HAVING MIN (HP) < 120; --- --- Fiat red Audi blue Skoda red Rena.ult red Figure 171: HAVING
)> What is the number of cars per brand, which are black or red?
)> Rename the Brand column to "Manufacturer"
)> Display only those manufacturers with at least 2 cars )> Sort the result set first descending by number of cars 1> Sort the result set second ascending by manufacturer.
SELECT c.Brand AS 11Manufacturer", COUNT(*)
FROM Car c
WHERE Col.or IN ( 'bl.ack' , 'red' ) GROUP BY Brand COUNT(*) --- 1 1 2 2 HAVING COUNT(*) >= 2
ORDER BY 2 DESC, Brand ASC; Manufacturer COUNT(*)
Figure 172: SELECT statement
© 2014 SAP AG or an SAP affiliate company. All rights reserved.
Skoda 3
vw 3
Renault 2
HA150 Lesson: Aggregating Data
Exercise 2: Exercise 2
Exercise Objectives
After completing this exercise, you will be able to:
• perform aggregations an a database table
• use groupings and filter groups using the HAVfNG clause
Business Example
Task:
I . What is the number of ernployees?
2. Ho\V many employees have exactly 20 remaining vacation days? 3. In how many different departments do the employees work? 4. What is the maximum number of remaining vacation days?
5. What is the average number of remaining vacation days? Employees whose remaining vacation days are unkno\vn should not be taken into account.
6. Ho\\r many remaining vacation days have the employees accumulated in total?
7. The minimum, average, maximum, and total number of remaining vacation days should be displayed with a single query.
8. What is the deviation of maximum remaining vacation days to the average of re1naining vacation days?
9. The total number of remaining vacation days should be displayed for each department.
1.0. The total number of remaining vacation days should be displayed for each department. Only departn1ents with more than 30 days should be taken into account. The display of the total number of days should be sorted descending.
�
Unit 3: Aggregating Data HA150
78
Solution 2: Exercise 2
Task:
l . What is the number of employees?
a) SELECT COUNT(*) FROM Employee;
2. How many employees have exactly 20 remaining vacation days?
a) SELECT COUNT(*) FROM Employee WHERE RemainderDays = 20;
3. In ho\V many different departments do the employees work? a) SELECT COUNT(DISTINCT DepID) FROM Employee; 4. What is the 1naximum number of remaining vacation days?
a) SELECT MAX(RemainderDays) FROM Employee;
5. What is the average nu1nber of remaining vacation days? Employees \Vhose remaining vacation days are unknown should not be taken into account. a) SELECT AVG(RemainderDays) FROM E1nployee;
6. How many remaining vacation days have the employees accu.mulated in total?
a) SELECT SUM(RemainderDays) FROM E1nployee;
7. The minimum, average, maximum, and total number of remaining vacation days should be displayed with a single query.
a) SELECT MTN(RemainderDays) AS "min", AVG(RemainderDays) AS "avg", MAX(RemainderDays) AS "max", SUM(RemainderDays) AS "sum" FROM Employee;
8. What is the deviation of maximum re1naining vacation days to the average of remaining vacation days?
a) SELECT MAX(RemainderDays) -AVG(RemainderDays) FROM Employee;
9. The total number of remaining vacation days should be displayed for each department.
a) SELECT DepID, SUM(RemainderDays) FROM Employee GROUP
BY DepID;
10. The total number of remaining vacation days should be displayed for each depart1nent. Only departments with more than 30 days should be taken into account. The display of the total number of days should be sorted descending. a) SELECT DepJD, SUM(RemainderDays) FROM Employee GROUP
BY DepID HAVING SUM(RemainderDays) > 30 ORDER BY 2 DESC;
© 2014 SAP AG or an SAP affiliate company.
HA150 Lesson: Aggregating Data
Lesson Summary You should now be able to:
• Detennine aggregated values on table columns using a single SELECT
statement.
• List the aggregate functions supported by HANA.
• Detennine such aggregated values for groups of rows, using the GROUP
BY clause.
Unit Summary
�
80
HA150
Unit Summary
You should now be able to:
• Determine aggregated values on table cohunns using a single SELECT
statement.
• List the aggregate functions supported by HANA.
• Determine such aggregated values for groups of ro,vs, using the GROUP
BY clause.
• Filter such groups using the HAVING clause.
© 2014 SAP AG or an SAP affiliate company.
n1
Reading Data From Multiple Tables
Part I
Unit Overview
Unit Objectives
After completing this unit, you will be able to:
• Merge the result of several sele ct statements using the UNION statement.
• Combine data from several tables when querying data using JOIN constructs. • List the various types of Joins.
• Explain the differences behvcen the various types of Joins, and apply the
right type of Join depending on the problem.
Unit Contents
Lesson: Reading Data from Multiple Tables- Part 1 .... . . 82 Exercise 3: Exercise 3 . . . 105