-- 2.16
SELECT avg(comm) FROM emp;
SELECT avg(isnull(comm, 0)) FROM emp;
SELECT avg(sal) FROM emp;
SELECT avg(distinct sal) FROM emp;

-- 2.17
SELECT count(1) as cnt, 
	CASE empno%2 
		WHEN 0 THEN 'even'
		WHEN 1 THEN 'odd'
	END as parity
FROM emp
GROUP BY
	CASE empno%2  
		WHEN 0 THEN 'even'
		WHEN 1 THEN 'odd'
	END;

SELECT 
	IIF(empno%2=0, 'EVEN', 'ODD') as parity,
	COUNT(1) as numRecords
FROM emp
GROUP BY IIF(empno%2=0, 'EVEN', 'ODD')
ORDER BY parity;

-- 2.22
SELECT round(avg(sal), 1) as avgSal, deptno
FROM emp
WHERE hiredate >= '1981-01-01'
GROUP BY deptno
HAVING avg(sal)>=1000
ORDER BY avgSal;

-- 2.23
SELECT round(avg(sal), 0) as avgSal, job, replicate('*', round(avg(sal)/200, 0)) as stars
FROM emp
WHERE hiredate > '1981-12-31'
GROUP BY job
HAVING avg(sal)<=3000
ORDER BY avgSal;

-- Adventureworks

SELECT sum(TotalDue) as total, convert(date, OrderDate) as day
FROM Sales.SalesOrderHeader
GROUP BY convert(date, OrderDate)
ORDER BY day;

SELECT sum(TotalDue) as total, format(OrderDate, 'yyyy-MM') as month
FROM Sales.SalesOrderHeader
GROUP BY format(OrderDate, 'yyyy-MM')
ORDER BY month;

SELECT sum(TotalDue) as total, year(OrderDate) as year
FROM Sales.SalesOrderHeader
GROUP BY year(OrderDate) 
ORDER BY year;


SELECT sum(TotalDue) as TotalSum,
	datepart(year, OrderDate) as year,
	datepart(month, OrderDate) as month,
	datepart(day, OrderDate) as day,
	grouping_id(datepart(year, OrderDate), datepart(month, OrderDate), datepart(day, OrderDate)) as grid
FROM Sales.SalesOrderHeader
GROUP BY ROLLUP(datepart(year, OrderDate), datepart(month, OrderDate), datepart(day, OrderDate))
ORDER BY grid;
-- ORDER BY year, month, day;

WITH data AS (
	SELECT TotalDue, 
			datepart(year, OrderDate) as year, 
			datepart(month, OrderDate) as month,
			datepart(day, OrderDate) as day
	FROM Sales.SalesOrderHeader
)
SELECT sum(TotalDue), year, month, day 
FROM data
GROUP BY ROLLUP (year, month, day)
ORDER BY year, month, day;

-- Sybase, MSSQL: TOP
-- Sybase: START AT
-- Mysql, Postgre: LIMIT / LIMIT+OFFSET
-- Oracle: ROWNUM
-- SQL:2003: Window/Analytical functions => ROW_NUMBER()
-- SQL:2008: OFFSET+FETCH

SELECT 
	TOP 10
	sum(TotalDue) as total, convert(date, OrderDate) as day
FROM Sales.SalesOrderHeader
GROUP BY convert(date, OrderDate)
ORDER BY day desc;

SELECT 
	sum(TotalDue) as total, convert(date, OrderDate) as day
FROM Sales.SalesOrderHeader
GROUP BY convert(date, OrderDate)
ORDER BY day desc
OFFSET 10 rows 
FETCH FIRST 20 rows only;

-- ADVANCED STUFF: OFFSET is slow => use WHERE instead
-- ADVANCED STUFF: Weekly stats (yyyy-weeknumber) => NOT trivial, yearweek in TSQL

-- ------------- SNIP, extra exercises

-- 2.18
-- convert(int, round(sal/1000, 0))
-- cast(round(sal/1000, 0) as int)
SELECT convert(int, round(sal/1000, 0)) as salCat, count(1) as numWorkers
FROM emp
GROUP BY round(sal/1000, 0);

-- 2.19 + 2.20
SELECT mgr, min(sal) as minSal, avg(sal) as avgSal
FROM emp
WHERE mgr is not null
GROUP BY mgr
HAVING min(sal)<=2000
ORDER BY avgSal;

-- 2.24
SELECT datediff(month, hiredate, getdate()) as months, count(1) as workerNum,
	cast(datediff(month, hiredate, getdate()) as float)/60 as charNum,
	replicate('#', round(cast(datediff(month, hiredate, getdate()) as float)/60, 0)) as graph
FROM emp
GROUP BY datediff(month, hiredate, getdate())
HAVING count(1)>=2
ORDER BY months;

-- ------------------------ AdventureWorks

SELECT City, count(1) as numEntries
FROM Person.Address
-- WHERE isnull(AddressLine2, '')=''
WHERE AddressLine2='' OR AddressLine2 is null
GROUP BY City
HAVING count(1)>1
ORDER BY numEntries desc;

SELECT Color, avg(ListPrice) as avgPrice
FROM Production.Product
WHERE Color is not null AND ListPrice<>0
GROUP BY Color;

SELECT ProductID, sum(OrderQty) as sumQty, sum(LineTotal) as sumTotal
FROM Sales.SalesOrderDetail
GROUP BY ProductID
ORDER BY sumTotal desc;