SQL Workshop Summaries Doug Shook Aggregates Also called column - PowerPoint PPT Presentation

SQL Workshop  Summaries Doug Shook

Aggregates  Also called column functions – AVG, SUM, MIN, MAX, COUNT  Will use all values – can use distinct if desired – All except COUNT ignore null values A summary query that counts unpaid invoices and calculates the total due SELECT COUNT(*) AS NumberOfInvoices, SUM(InvoiceTotal - PaymentTotal - CreditTotal) AS TotalDue FROM Invoices WHERE InvoiceTotal - PaymentTotal - CreditTotal > 0; The result set 2

Aggregates A summary query with COUNT(*), AVG, and SUM SELECT 'After 9/1/2011' AS SelectionDate, COUNT(*) AS NumberOfInvoices, AVG(InvoiceTotal) AS AverageInvoiceAmount, SUM(InvoiceTotal) AS TotalInvoiceAmount FROM Invoices WHERE InvoiceDate > '2011-09-01'; The result set A summary query with MIN and MAX SELECT 'After 9/1/2011' AS SelectionDate, COUNT(*) AS NumberOfInvoices, MAX(InvoiceTotal) AS HighestInvoiceTotal, MIN(InvoiceTotal) AS LowestInvoiceTotal FROM Invoices WHERE InvoiceDate > '2011-09-01'; The result set 3

Aggregates A summary query for non-numeric columns SELECT MIN(VendorName) AS FirstVendor, MAX(VendorName) AS LastVendor, COUNT(VendorName) AS NumberOfVendors FROM Vendors; The result set A summary query with the DISTINCT keyword SELECT COUNT(DISTINCT VendorID) AS NumberOfVendors, COUNT(VendorID) AS NumberOfInvoices, AVG(InvoiceTotal) AS AverageInvoiceAmount, SUM(InvoiceTotal) AS TotalInvoiceAmount FROM Invoices WHERE InvoiceDate > '2011-09-01'; The result set 4

Aggregates  What do you notice about the previous examples?  Three exceptions – Literals – GROUP BY – OVER 5

GROUP BY  Commonly used with aggregates – Groups the rows based on a certain column  Commonly found with a HAVING clause – Specifies a search condition  Allows for aggregates and non-aggregates to be used together – Non-aggregate columns will be used for groupings 6

GROUP BY A summary query that calculates the average invoice amount by vendor SELECT VendorID, AVG(InvoiceTotal) AS AverageInvoiceAmount FROM Invoices GROUP BY VendorID HAVING AVG(InvoiceTotal) > 2000 ORDER BY AverageInvoiceAmount DESC; The result set 7

GROUP BY A summary query that counts the number of invoices by vendor SELECT VendorID, COUNT(*) AS InvoiceQty FROM Invoices GROUP BY VendorID; The result set (34 rows) 8

GROUP BY A summary query that groups by two columns SELECT VendorState, VendorCity, COUNT(*) AS InvoiceQty, AVG(InvoiceTotal) AS InvoiceAvg FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID GROUP BY VendorState, VendorCity ORDER BY VendorState, VendorCity; The result set (20 rows) 9

GROUP BY The same summary query with a HAVING clause SELECT VendorState, VendorCity, COUNT(*) AS InvoiceQty, AVG(InvoiceTotal) AS InvoiceAvg FROM Invoices JOIN Vendors ON Invoices.VendorID = Vendors.VendorID GROUP BY VendorState, VendorCity HAVING COUNT(*) >= 2 ORDER BY VendorState, VendorCity; The result set (12 rows) 10

HAVING vs. WHERE  Both serve the same general purpose – Application is different  WHERE is applied before rows are grouped and aggregates are calculated – HAVING is applied after  WHERE can refer to any column – HAVING can only refer to columns in SELECT or GROUP BY  WHERE cannot contain aggregates – HAVING can 11

HAVING vs. WHERE A summary query with a search condition in the HAVING clause SELECT VendorName, COUNT(*) AS InvoiceQty, AVG(InvoiceTotal) AS InvoiceAvg FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID GROUP BY VendorName HAVING AVG(InvoiceTotal) > 500 ORDER BY InvoiceQty DESC; The result set (19 rows) 12

HAVING vs. WHERE A summary query with a search condition in the WHERE clause SELECT VendorName, COUNT(*) AS InvoiceQty, AVG(InvoiceTotal) AS InvoiceAvg FROM Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID WHERE InvoiceTotal > 500 GROUP BY VendorName ORDER BY InvoiceQty DESC; The result set (20 rows) 13

HAVING vs. WHERE A summary query with a compound condition in the HAVING clause SELECT InvoiceDate, COUNT(*) AS InvoiceQty, SUM(InvoiceTotal) AS InvoiceSum FROM Invoices GROUP BY InvoiceDate HAVING InvoiceDate BETWEEN '2012-01-01' AND '2012-01-31' AND COUNT(*) > 1 AND SUM(InvoiceTotal) > 100 ORDER BY InvoiceDate DESC; The result set 14

HAVING vs. WHERE The same summary query with a WHERE clause SELECT InvoiceDate, COUNT(*) AS InvoiceQty, SUM(InvoiceTotal) AS InvoiceSum FROM Invoices WHERE InvoiceDate BETWEEN '2012-01-01' AND '2012-01-31' GROUP BY InvoiceDate HAVING COUNT(*) > 1 AND SUM(InvoiceTotal) > 100 ORDER BY InvoiceDate DESC; The same result set 15

Exercises  Write a SELECT statement that returns two columns from the Invoices table: VendorID and PaymentSum, where PaymentSum is the sum of the PaymentTotal column. Group the result set by VendorID.  Write a SELECT statement that returns two columns: VendorName and PaymentSum, where PaymentSum is the sum of the PaymentTotal column. Group the result set by VendorName. Return only 10 rows, corresponding to the 10 vendors who've been paid the most. 16

Exercises  Write a SELECT statement that returns three columns: VendorName, InvoiceCount, and InvoiceSum. InvoiceCount is the count of the number of invoices, and InvoiceSum is the sum of the InvoiceTotal column. Group the result set by vendor. Sort the result set so that the vendor with the highest number of invoices appears first. 17

Exercises  Write a SELECT statement that returns three columns: AccountDescription, LineItemCount, and LineItemSum. LineItemCount is the number of entries in the InvoiceLineItems table that have that AccountNo. LineItemSum is the sum of the InvoiceLineItemAmount column for that AccountNo. Filter the result set to include only those rows with LineItemCount greater than 1. Group the result set by account description, and sort it by descending LineItemCount. Hint: Join the GLAccounts table to the InvoiceLineItems table. 18

Exercises  Modify the solution to the previous problem to filter for invoices dated from December 1, 2011 to February 29, 2012. 19

Exercises  Write a SELECT statement that returns four columns: VendorName, AccountDescription, LineItemCount, and LineItemSum. LineItemCount is the row count, and LineItemSum is the sum of the InvoiceLineItemAmount column. For each vendor and account, return the number and sum of line items, sorted first by vendor, then by account description. Hint: use a four table join. 20

Exercises  Write a SELECT statement that answers this question: Which vendors are being paid from more than one account? Return two columns: the vendor name and the total number of accounts that apply to that vendor's invoices. Hint: Use the DISTINCT keyword to count InvoiceLineItems.AccountNo. 21