/*
CIS 324

CHAPTER 7

Aggregate functions

*/


/*
Aggregate functions pp. 255
AVG(), MAX(), MIN(), SUM(), COUNT(*)
These functions are the mainstay of reports.

COUNT(*)                    counts all rows in the result
COUNT(Attribute)            counts all occurrences of an attribute excluding NULL values
COUNT(DISTINCT Attribute)   counts distinct occurrences of an attribute excluding NULL values

Count the number of orders in the Northwind database

Count the number of times the "Run" button has been pressed in the SQL Exercise 
system in the last two years (since my last unrecovered hard drive failure...)

*/



USE SqlExerciseSystem

SELECT  COUNT(*) AS RunCount, MIN(SubmitTime) as firstrun
FROM tblExerciseResponse

USE Northwind

--This formulation counts all rows
SELECT COUNT(*) AS CustomerCount 
FROM Customers

--If you look in the lower hand corner, number of rows is reported
--for every query
SELECT *
FROM Customers

--This formulation counts all rows excluding HUNGO. Why?
SELECT COUNT(PostalCode) AS PostalCount
FROM Customers

SELECT PostalCode FROM Customers ORDER BY PostalCode

--This formulation counts all distinct rows excluding HUNGO
SELECT COUNT(DISTINCT PostalCode) AS PostalCount
FROM Customers

/*
The sales dept would like to know the average freight 
cost for all orders 
*/

SELECT *, Freight FROM Orders

SELECT AVG(Freight) AS AvgFreight
FROM Orders

--What is the average for employeeid 5?
SELECT AVG(Freight) AS AvgFreight
FROM Orders
WHERE EmployeeID = 5

/*
AVG, by default, returns an integer result when
you average an integer value. Such as...
*/

SELECT * FROM Orders
SELECT AVG(EmployeeID) AS AvgEmployeeID FROM Orders

SELECT AVG(CAST(EmployeeID AS REAL)) AS AvgEmployeeID FROM Orders

/*
General statistics for Order Details
*/

SELECT MAX(UnitPrice) AS MaxUnitPrice,
       MIN(UnitPrice) AS MinUnitPrice,
       AVG(UnitPrice) AS AvgUnitPrice,
       COUNT(DISTINCT UnitPrice) AS DistinctUnitPrice 
FROM [Order Details]