Advanced Summary Queries

1
Advanced Summary Queries

• CIT 381

2
GROUP BY
Find the average and total order size for each
Sales Rep. First find the order amounts of
each Sales Rep. SELECT s.name, o.amount FROM
salesreps s, orders o WHERE s.empl_numo.rep ORDER
BY 1
3
Apply Function to Each Group
SELECT s.name, AVG(o.amount) as AvgSales,
SUM(o.amount) AS TotalSales FROM
salesreps s, orders o WHERE s.empl_numo.rep GROUP
BY s.name
AVG and SUM were applied to each group with same
name.
4
Using Outer Join to Count Zeroes
Find the number of orders of each
company (including those who have ordered
zero). First, lets think how an outer join
can list each companys orders. SELECT
c.company, o.order_num FROM customers c LEFT JOIN
orders o ON c.cust_numo.cust ORDER BY 1
5
Notice the groups, from the ORDER BY
6
SELECT c.company, count() FROM customers c LEFT
JOIN orders o ON c.cust_numo.cust GROUP BY
c.company This mis-counts those who
ordered Nothing (such as AAA)
7
SELECT c.company, count(o.order_num) AS
NumOrders FROM customers c LEFT JOIN orders o ON
c.cust_numo.cust GROUP BY c.company
8
HAVING Clause

• A HAVING clause allows one to apply a condition
  to the results of the aggregate
• Remember order of computation
• FROM, pushes cross-product of tables into
• WHERE, surviving rows go to
• GROUP BY, forming groups on which
• aggregates are computed, from which
• HAVING picks out desired rows (like WHERE),
• then we SELECT desired attributes

9
Example of HAVING
Get the average and total sales of those Reps
with over 30k in total sales. SELECT s.name,
AVG(o.amount) as AvgSales, SUM(o.amount) AS
TotalSales FROM salesreps s, orders o WHERE
s.empl_numo.rep GROUP BY s.name HAVING
SUM(o.amount)30000
10
More Examples from SQL Text
For each office with two or more people,
compute the total quota and total sales for all
salespeople who work in the office. SELECT
city, sum(quota), sum(salesreps.sales) FROM
offices, salesreps WHERE officerep_office GROUP
BY city HAVING count()2
11
Show the price, quantity on hand, and total
quantity on order for each product where the
total quantity on order is more than 75 per cent
of the quantity on hand. SELECT description,
price, qty_on_hand, sum(qty) FROM products,
orders WHERE mfrmfr_id AND productproduct_id GRO
UP BY mfr_id, product_id, description,
price, qty_on_hand HAVING SUM(qty)
(.75qty_on_hand) ORDER BY 3 DESC
12
Result of previous query.

• Notice the bad style no renaming of tables
• Can get away with it here unique attribute
  names
• DESC will sort descending (ASC is default)
• Any attributes in SELECT clause must appear
• in GROUP BY clause.