Transact SQL 101 - Lesson 08_01_SQL.txt

USE adventureworkslt
GO

SELECT COUNT(customerid) from saleslt.customer
SELECT COUNT(*) FROM saleslt.customer

SELECT MAX(TotalDue) AS 'Max Total Due' FROM saleslt.salesorderheader
SELECT MIN(TotalDue) AS 'Min Total Due' FROM saleslt.salesorderheader

SELECT AVG(TotalDue) AS 'Avg Total Due' FROM saleslt.salesorderheader
SELECT SUM(TotalDue) AS 'Sum Total Due' FROM saleslt.salesorderheader

--Example 1
SELECT countryregion, stateprovince, COUNT(*) AS 'Total' 
FROM saleslt.address
WHERE countryregion = 'United States'
GROUP BY countryregion, stateprovince
HAVING count(*) > 25

SELECT countryregion, stateprovince 
FROM saleslt.address

--Example 2
SELECT customerid, COUNT(customerid), SUM(totaldue) AS 'Sum Total' 
FROM saleslt.salesorderheader
GROUP BY customerid
HAVING SUM(totaldue) > 10000
ORDER BY 'Sum Total' DESC

SELECT customerid, COUNT(customerid), SUM(totaldue) AS 'Sum Total' 
FROM saleslt.salesorderheader
ORDER BY 'Sum Total' DESC

--Example 3
SELECT a.CountryRegion, COUNT(s.customerid) AS 'Count', SUM(s.totaldue) AS 'Total'
FROM saleslt.salesorderheader s
	INNER JOIN saleslt.address a ON s.shiptoaddressid = a.addressid
GROUP BY a.CountryRegion
ORDER BY COUNT(s.customerid), SUM(s.totaldue) DESC

--Example 4
SELECT a.CountryRegion, a.StateProvince, COUNT(s.customerid), SUM(s.totaldue)
FROM saleslt.salesorderheader s
	INNER JOIN saleslt.address a ON s.shiptoaddressid = a.addressid
	WHERE s.totaldue > 15000
GROUP BY a.CountryRegion, a.StateProvince
HAVING COUNT(s.customerid) > 1
ORDER BY a.CountryRegion