Generate plain HTML

GROUP BY Command

GROUP BY

 is used to group rows that have the same values into summary rows

Must be used with an Aggregate function (ie. SUM, AVG, COUNT, MIN, MAX) 

SELECT COUNT(emplyee_id), city
FROM employees
GROUP BY city

ROLLUP()

 is used to generate multiple grouping sets


SELECT COUNT(emplyee_id), city, sales
FROM employees
GROUP BY ROLLUP(city, sales)

HAVING

 requires using a SUM(), COUNT(), AVG()


SELECT COUNT(employee_id), city
FROM employees
GROUP BY city
HAVING COUNT(employee_id) > 5

ISNULL()

 can change a NULL value into a 0 to avoid computational errors

Any value arithmetically processed with NULL results in NULL

Use GROUPING SETS over CUBE for 3 or more columns

TOP

Microsoft ONLY

Used with SELECT to grab the top results


SELECT TOP 5 order_id, product_id, quantity
FROM [order details]
ORDER BY quantity DESC

Used with TIES counts identical values as one


SELECT TOP 5 WITH TIES order_id, product_id, quantity
FROM [order details]
ORDER BY quantity DESC