Group by with Join Find the number of depositors for each branch select branch-name, count( distinct customer-name) from de epositor, account where depositor account-number = account account-number group by branch-name Perform Join then group by then count( distinct o) depositor(customer-name, account-number) account(branch-name, account-number, balance Join=(customer-name, account-number, branch-name balance) Group by and aggregate functions apply to the Join result COMP3311 Fall 2011 CSE, HKUST Slide 16
COMP3311 Fall 2011 CSE, HKUST Slide 16 • 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 • Perform Join then group by then count ( distinct () ) depositor (customer-name, account-number) account (branch-name, account-number, balance) Join (customer-name, account-number, branch-name, balance) • Group by and aggregate functions apply to the Join result Group by with Join
Group by Evaluation branch -namecust-name Perryridge John Wong select branch-name customer-name Perryridge Jacky Chal count ptown ohn Wong from depositor, account Uptown Mary Kwan where depositor account-number Downtown John Wong Downtown Pat Lee branch-name count account account-number I Downtown May Cheung ridge 2 Uptown distinct Downtown anch-name cust-name branch-name cust-name Perryridge John Wong Perryridge John Wong Downtown Pat Lee Perryridge Jacky Chan Uptown John Wong group by Perryridge John Wong 匚 Perryridge Jacky Chan Uptown John Wong ptown ary kwan Mary kwan Downtown John Wong Downtown John Wong Perryridge John Wong Downtown Pat Lee Downtown May Cheung Downtown May Cheung COMP3311 Fall 2011 CSE, HKUST Slide 17
COMP3311 Fall 2011 CSE, HKUST Slide 17 Group by Evaluation select branch-name, customer-name from depositor, account where depositor.account-number = account.account-number branch-name cust-name Perryridge John Wong Perryridge Jacky Chan Perryridge John Wong Uptown John Wong Downtown John Wong Uptown Mary Kwan Downtown Pat Lee Downtown May Cheung branch-name cust-name Perryridge John Wong Perryridge Jacky Chan Perryridge John Wong Uptown John Wong Downtown John Wong Uptown Mary Kwan Downtown Pat Lee Downtown May Cheung group by branch-name cust-name Perryridge John Wong Perryridge Jacky Chan Uptown John Wong Downtown John Wong Uptown Mary Kwan Downtown Pat Lee Downtown May Cheung distinct count branch-name Perryridge Uptown Downtown count 2 2 3
Having Clause Find the names of all branches where the average account balance is more than $700 select branch-name avg(balance) from account group by branch-name having avg(balance)>700 predicates in the having clause are applied to each group after the formation of groups branch- account- balance name number d a-102 400 Perryridge a-201 900 Brightor a217-1-750 Brighton a 750 Redwood a-222 700 COMP3311 Fall 2011 CSE, HKUST Slide 18
COMP3311 Fall 2011 CSE, HKUST Slide 18 Having Clause • Find the names of all branches where the average account balance is more than $700 select branch-name, avg(balance) from account group by branch-name having avg (balance) >700 • predicates in the having clause are applied to each group after the formation of groups branchname accountnumber balance Perryridge Perryridge Brighton Brighton Redwood a-102 a-201 a-217 a-215 a-222 400 900 750 750 700
Group-by Motivation Group-by permits us to display aggregate results(e.g, max, min, sum)for groups. For instance, if we have GROUP-BY X, we will get a result for every different value of X Recall that aggregate queries without group-by return just a single number. If we put an attribute in SELECT, the attribute must also appear in GROUP BY. The opposite is not true there may be attributes in GRoUP-BY that do not appear in SELECT Any condition that appears in WHERe is applied before the formation of groups-in other words, records that do not pass the Where condition are eliminated before the formation of groups Any condition that appears in HAVING refers to the groups and is applied after the formation of the groups. The condition must involve aggregate functions, or attributes that appear in the select or GROUP-BY lines COMP3311 Fall 2011 CSE, HKUST Slide 19
COMP3311 Fall 2011 CSE, HKUST Slide 19 Group-by • Motivation: Group-by permits us to display aggregate results (e.g., max, min, sum) for groups. For instance, if we have GROUP-BY X, we will get a result for every different value of X. • Recall that aggregate queries without group-by return just a single number. • If we put an attribute in SELECT, the attribute must also appear in GROUPBY. The opposite is not true: there may be attributes in GROUP-BY that do not appear in SELECT. • Any condition that appears in WHERE, is applied before the formation of groups – in other words, records that do not pass the WHERE condition are eliminated before the formation of groups. • Any condition that appears in HAVING refers to the groups and is applied after the formation of the groups. The condition must involve aggregate functions, or attributes that appear in the SELECT or GROUP-BY lines
Query 1: Find the total number of copies in stock for each poet poet book copies in stock Douglas livingstone The Skull 21 Douglas livingstone A Littoral zone 2 Mongane wally Stelo Mongane wall Must Weep Mongane wally A Tough Tale poet sum Douglas livingstone 23 Mongane Wally 13 SELECT poet, SUM(copies_in_stock) as sum FROM writer GRoUP BY poet COMP3311 Fall 2011 CSE, HKUST Slide 20
COMP3311 Fall 2011 CSE, HKUST Slide 20 Query 1: Find the total number of copies in stock for each poet SELECT poet, SUM (copies_in_stock) as sum FROM writer GROUP BY poet poet sum Douglas Livingstone 23 Mongane Wally 13 poet book copies_in_stock Douglas Livingstone The Skull 21 Douglas Livingstone A Littoral Zone 2 Mongane Wally Tstetlo 3 Mongane Wally Must Weep 8 Mongane Wally A Tough Tale 2