GROUP BY Deep Dive

-- Every column in SELECT that is NOT inside an aggregate
-- function MUST appear in GROUP BY.

-- CORRECT:
SELECT department, COUNT(*)
FROM employees
GROUP BY department;

-- WRONG (will error):
SELECT department, name, COUNT(*)
FROM employees
GROUP BY department;

"name" is not in GROUP BY and not in an aggregate — this errors in standard SQL (MySQL in non-strict mode may allow it but return arbitrary values).

SELECT department,
       COUNT(*) AS headcount,
       SUM(salary) AS total_salary
FROM employees
GROUP BY department
ORDER BY total_salary DESC;

Classic analytical query. This exact pattern appears in business reports, dashboards, and MIS sheets.

SELECT department,
       job_title,
       COUNT(*) AS count
FROM employees
GROUP BY department, job_title
ORDER BY department, count DESC;

You can GROUP BY multiple columns. The result is one row per unique combination of all grouped columns.

SELECT department,
       COUNT(*) AS headcount,
       SUM(salary) AS total_salary
FROM employees
GROUP BY department
HAVING SUM(salary) > 500000
   AND COUNT(*) > 5;

Multiple conditions in HAVING use AND/OR just like WHERE. This type of query is used in HR and finance reporting.

-- COUNT(*): counts all rows including NULLs
SELECT COUNT(*) FROM employees;

-- COUNT(1): same as COUNT(*), just a constant
SELECT COUNT(1) FROM employees;

-- COUNT(column): counts non-NULL values only
SELECT COUNT(manager_id) FROM employees;

COUNT(*) and COUNT(1) give the same result and similar performance. COUNT(column) is different — it skips NULLs. Common interview trick question.

SELECT YEAR(hire_date) AS hire_year,
       COUNT(*) AS hired
FROM employees
GROUP BY YEAR(hire_date)
ORDER BY hire_year;

YEAR() extracts the year from a date. In PostgreSQL use EXTRACT(YEAR FROM hire_date). This is a common trend analysis query.