Aggregate Functions Operate on 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 Note: for our examples we use the tables Branch(branch-name, branch-city, assets) Customer(customer-name, customer-street, customer-city Loan(loan- number, amount, branch-name Account( account-number, balance, branch-name Borrower(customer-name, loan-number Depositor(customer-name, account-number COMP3311 Fall 2011 CSE, HKUST Slide 11
COMP3311 Fall 2011 CSE, HKUST Slide 11 Aggregate Functions • Operate on 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 • Note: for our examples we use the tables: – Branch (branch-name, branch-city, assets) – Customer (customer-name, customer-street, customer-city) – Loan (loan-number, amount, branch-name) – Account (account-number, balance, branch-name) – Borrower (customer-name, loan-number) – Depositor (customer-name, account-number)
Aggregate Function Computation Find the average account balance at the perryridge branch select avg(balance) from account where branch-name="Perryridge Balances of perryridge accounts account select balance from account where branch-name AvgO Perryridge 120.000 COMP3311 Fall 2011 CSE, HKUST Slide 12
COMP3311 Fall 2011 CSE, HKUST Slide 12 Aggregate Function Computation • Find the average account balance at the Perryridge branch. select avg(balance) from account where branch-name=“Perryridge” account select balance from account where branch-name =“Perryridge” Avg() 120,000 Balances of Perryridge accounts
Examples of Aggregate Functions Find the numbers of tuples in the customer relation select count (* from customer remember stands for all attributes Same as. select count(customer-city) from customet Different from select count(distinct customer-city from customer Because customer-City is not a key COMP3311 Fall 2011 CSE, HKUST Slide 13
COMP3311 Fall 2011 CSE, HKUST Slide 13 Examples of Aggregate Functions • Find the numbers of tuples in the customer relation. select count(*) from customer – remember * stands for all attributes – Same as: select count(customer-city) from customer – Different from: select count(distinct customer-city) from customer – Because customer-city is not a key
Group by Find the number of accounts for each branch select branch-name count(account-number) from account group by branch-name For each group of tuples with the same branch-name, apply aggregate function count and distinct to account-number branch-name account-number balance branch-name account-number balance Perryridge a-102 400 Perryridge a-102 400 Brighte a-217 750 Perryridge 900 Perryridge a-201 900 Brighton -217 750 Brighton a-215 750 Brighton gn a-215 750 Redwood a-222 700 Redwood 700 branch-name count-account-no account table Perryridge Brighton 2 Redwood COMP3311 Fall 2011 CSE, HKUST Slide 14
COMP3311 Fall 2011 CSE, HKUST Slide 14 Group by • Find the number of accounts for each branch. select branch-name, count(account-number) from account group by branch-name • For each group of tuples with the same branch-name, apply aggregate function count and distinct to account-number branch-name count-account-no Perryridge Brighton Redwood 2 2 1 branch-name account-number balance Perryridge Brighton Perryridge Brighton Redwood a-102 a-217 a-201 a-215 a-222 400 750 900 750 700 branch-name account-number balance Perryridge Perryridge Brighton Brighton Redwood a-102 a-201 a-217 a-215 a-222 400 900 750 750 700 account table
Group by Attributes Attributes in select clause outside of aggregate functions must appear in group by list, why lect branch -name balane, count( distinct account-number) from account- group-by branch-name correct branch account balance select from account name number group by branch-name, balance Perryridge 102 400 P tyro dge 20 900 OR Brighton a-217 750 select branch-name, sum(balance), count(.) Brighton a-215 75 Redwood a-222 700 from account group by branch-name COMP3311 Fall 2011 CSE, HKUST Slide 15
COMP3311 Fall 2011 CSE, HKUST Slide 15 • Attributes in select clause outside of aggregate functions must appear in group by list, why? select branch-name, balance, count( distinct account-number) from account group by branch-name branchname accountnumber balance Perryridge Perryridge Brighton Brighton Redwood a-102 a-201 a-217 a-215 a-222 400 900 750 750 700 select … from account group by branch-name, balance OR select branch-name, sum(balance), count(…) from account group by branch-name Group by Attributes correct