Aggregate Functions These functions operate on the multiset of values of a column of a relation, and return a value avg: average value min: minimum value max: maximum value sum: sum of values count: number of values 标 Database System Concepts 4.16 @Silberschatz, Korth and Sudarshan
Database System Concepts 4.16 ©Silberschatz, Korth and Sudarshan Aggregate Functions These functions operate on the multiset of values of a column of a relation, and return a value avg: average value min: minimum value max: maximum value sum: sum of values count: number of values
Aggregate Functions(Cont) Find the average account balance at the Perryridge branch select avg(balance) from account where branch-name ="Perryridge Find the number of tuples in the customer relation select count ( from customer Find the number of depositors in the bank select count (distinct customer-name) from depositor Database System Concepts 4.17 @Silberschatz, Korth and Sudarshan
Database System Concepts 4.17 ©Silberschatz, Korth and Sudarshan Aggregate Functions (Cont.) Find the average account balance at the Perryridge branch. Find the number of depositors in the bank. Find the number of tuples in the customer relation. select avg (balance) from account where branch-name = ‘Perryridge’ select count (*) from customer select count (distinct customer-name) from depositor
Aggregate Functions- Group By Find the number of depositors for each branch select branch-name, count (distinct customer-name) from depositor, account where depositor account-number account account-number group by branch-name Note: Attributes in select clause outside of aggregate functions must appear in group by list 标 Database System Concepts 4.18 @Silberschatz, Korth and Sudarshan
Database System Concepts 4.18 ©Silberschatz, Korth and Sudarshan Aggregate Functions – Group By Find the number of depositors for each branch. Note: Attributes in select clause outside of aggregate functions must appear in group by list select branch-name, count (distinct customer-name) from depositor, account where depositor.account-number = account.account-number group by branch-name
Aggregate Functions- Having Clause Find the names of all branches where the average account balance is more than $1, 200 select branch-name, avg(balance) from account group by branch-name having avg(balance)> 1200 Note: predicates in the having clause are applied after the formation of groups whereas predicates in the where clause are applied before forming groups Database System Concepts 4.19 @Silberschatz, Korth and Sudarshan
Database System Concepts 4.19 ©Silberschatz, Korth and Sudarshan Aggregate Functions – Having Clause Find the names of all branches where the average account balance is more than $1,200. Note: predicates in the having clause are applied after the formation of groups whereas predicates in the where clause are applied before forming groups select branch-name, avg (balance) from account group by branch-name having avg (balance) > 1200
Null values It is possible for tuples to have a null value, denoted by nul, for some of their attributes null signifies an unknown value or that a value does not exist The predicate is null can be used to check for null values E.g. Find all loan number which appear in the loan relation with null values for amount select loan-number from loan where amount is null The result of any arithmetic expression involving null is null E.g. 5+ null returns null Database System Concepts 4.20 @Silberschatz, Korth and Sudarshan
Database System Concepts 4.20 ©Silberschatz, Korth and Sudarshan Null Values It is possible for tuples to have a null value, denoted by null, for some of their attributes null signifies an unknown value or that a value does not exist. The predicate is null can be used to check for null values. E.g. Find all loan number which appear in the loan relation with null values for amount. select loan-number from loan where amount is null The result of any arithmetic expression involving null is null E.g. 5 + null returns null