The GROUP BY clause is usually used with an aggregate function (COUNT, SUM, AVG, MIN, MAX). It groups the rows by a given column value (specified after GROUP BY) then calculates the aggregate for each group and returns that to the screen.
SELECT column1, COUNT(column2) FROM table_name GROUP BY column1;
This query counts the number of values in column2 – for each group of unique column1 values.
SELECT column1, SUM(column2) FROM table_name GROUP BY column1;
This query sums the number of values in column2 – for each group of unique column1 values.
SELECT column1, MIN(column2) FROM table_name GROUP BY column1;
This query finds the minimum value in column2 – for each group of unique column1 values.
SELECT column1, MAX(column2) FROM table_name GROUP BY column1;
This query finds the maximum value in column2 – for each group of unique column1 values.
SQL ALIASES
You can rename columns, tables, subqueries, anything.
SELECT column1, COUNT(column2) AS number_of_values FROM table_name
GROUP BY column1;
This query counts the number of values in column2 – for each group of unique column1 values. Then it renames the COUNT(column2) column to
number_of_values.
SQL JOIN
You can JOIN two (or more) SQL tables based on column values.
SELECT *
FROM table1
JOIN table2
ON table1.column1 = table2.column1;
This joins table1 and table2 values – for every row where the value of column1 from table1 equals the value of column1 from table2.
SQL HAVING
The execution order of the different SQL keywords doesn’t allow you to filter with the WHERE clause on the result of an aggregate function (COUNT, SUM, etc.). This is because WHERE is executed before the aggregate functions. But that’s what HAVING is for:
SELECT column1, COUNT(column2)
FROM table_name
GROUP BY column1
HAVING COUNT(column2) > 100;
This query counts the number of values in column2 – for each group of unique column1 values. It returns only those results where the counted value is greater than 100.
CORRECT KEYWORD ORDER AGAIN
SQL is extremely sensitive to keyword order. So make sure you keep it right:
1. SELECT
2. FROM
3. JOIN (ON)
4. WHERE
5. GROUP BY
6. HAVING
7. ORDER BY
8. LIMIT
SUBQUERIES
You can run SQL queries within SQL queries. (Called subqueries.) Even queries
within queries within queries. The point is to use the result of one query as an input
value of another query.
Example:
SELECT COUNT(*) FROM
(SELECT column1, COUNT(column2) AS inner_number_of_values
FROM table_name
GROUP BY column1) AS inner_query
WHERE inner_number_of_values > 100;
The inner query counts the number of values in column2 – for each group of unique column1 values. Then the outer query uses the inner query’s results and counts the number of values where inner_number_of_values are greater than 100. (The result is one number.)
