Chapter 5 Answers Grouping/Windowing

1)  First, use this code to create the table that we're trying to query

USE TESTDB
GO
CREATE TABLE chapter5 (
ID INT,
Name VARCHAR(10));
USE TestDB
INSERT INTO chapter5 (ID, Name)
VALUES
(1, 'Alpha'),
(2, 'Beta'),
(3, 'Beta'),
(4, 'Beta'),
(5, 'Charlie'),
(6, 'Charlie')

Here's the query itself

USE TestDB
SELECT NAME, COUNT(ID) AS cnt
FROM chapter5
WHERE ID IS NOT NULL
GROUP BY Name
UNION ALL
SELECT 'SUM', COUNT(ID)
FROM chapter5
--
2)  Here's my answer, then the actual answer below it.  slight differences but produces same results

USE TSQL2012
SELECT shipperID, COUNT(shipperid) AS shipcount
FROM sales.orders
WHERE shipperid IS NOT NULL
GROUP BY shipperid
--
USE TSQL2012
SELECT shipperID, COUNT(*) AS numorders
FROM sales.orders
GROUP BY shipperid

3)
USE TSQL2012
SELECT shipperid, COUNT(*) AS numorder, YEAR(shippeddate) AS shippedyear
FROM sales.Orders
WHERE shippeddate IS NOT NULL
GROUP BY shipperid, YEAR(shippeddate)
HAVING COUNT(*) < 100

Exercise 1)
USE TSQL2012
SELECT c.custid, COUNT(*) AS numorders
FROM sales.Customers AS C
INNER JOIN sales.Orders AS O
ON c.custid = o.custid
WHERE c.country = 'Spain'
GROUP BY c.custid, c.city

Exercise 2) Working on it before I post so that I'm comfortable with it

Cheers