SQL BY

What is the BY keyword used for in SQL?

BY is a reserved word used inside GROUP BY, ORDER BY, and PARTITION BY clauses to specify how result sets are grouped, sorted, or partitioned.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL BY: PostgreSQL, MySQL, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift, DB2: fully supported.

SQL BY Full Explanation

In SQL, BY is never used alone. It forms part of three fundamental clauses that control result-set organization:1. GROUP BY tells the database engine to aggregate rows that share identical values in the listed columns or expressions. It works together with aggregate functions such as COUNT, SUM, AVG, MIN, and MAX.2. ORDER BY sorts the returned rows according to the listed columns or expressions, optionally using ASC (ascending) or DESC (descending) modifiers. ORDER BY always executes after SELECT, FROM, WHERE, and GROUP BY have produced the intermediate result set.3. PARTITION BY appears in window functions (OVER clause) and divides the window into subsets, allowing calculations such as running totals or rankings to restart for every partition.Because BY modifies how data is arranged, it can materially affect query performance. Appropriate indexes and selective column order matter, especially for ORDER BY and GROUP BY on large tables. In standard SQL you may reference column aliases in ORDER BY but not in GROUP BY. Most engines evaluate ORDER BY after DISTINCT and LIMIT/OFFSET, while GROUP BY occurs before these stages.Caveats:- All nonaggregated columns in the SELECT list must appear in GROUP BY (or be functionally dependent) to avoid errors.- ORDER BY on a view or subquery with its own ORDER BY is ignored unless combined with TOP, LIMIT, or FETCH.- PARTITION BY requires an accompanying ORDER BY inside the window for functions that depend on row order (e.g., LAG, LEAD, ROW_NUMBER).

SQL BY Syntax

-- GROUP BY
SELECT column1, SUM(column2)
FROM   table_name
GROUP BY column1;

-- ORDER BY
SELECT *
FROM   table_name
ORDER BY column1 ASC, column2 DESC;

-- PARTITION BY within a window function
SELECT employee_id,
       department_id,
       salary,
       AVG(salary) OVER (PARTITION BY department_id) AS avg_dept_sal
FROM   employees;

SQL BY Parameters

column_list (list) - One or more column names or expressions that dictate grouping, sorting, or partitioning.
sort_direction (keyword) - Optional ASC or DESC modifier for ORDER BY.
rollup_cube (keyword) - Optional ROLLUP or CUBE extensions to GROUP BY in some dialects.

Example Queries Using SQL BY

-- 1. Total sales per product ordered by revenue desc
SELECT product_id,
       SUM(amount) AS revenue
FROM   sales
GROUP BY product_id
ORDER BY revenue DESC;

-- 2. Running total of orders per customer
SELECT order_id,
       customer_id,
       order_total,
       SUM(order_total) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM   orders;

-- 3. Distinct users per country with alphabetical sort
SELECT country,
       COUNT(DISTINCT user_id) AS users
FROM   user_profiles
GROUP BY country
ORDER BY country;

Expected Output Using SQL BY

First query returns one row per product with an aggregated revenue column, sorted from highest to lowest revenue.
Second query returns every order plus a running_total column that resets for each customer.
Third query lists each country once with its user count, sorted alphabetically.

Use Cases with SQL BY

Aggregating metrics like revenue, counts, or averages per dimension.
Sorting final results for reports, dashboards, or pagination.
Window analytic calculations such as rankings, moving averages, and running totals that must reset per logical group.

Common Mistakes with SQL BY

Selecting nonaggregated columns not listed in GROUP BY (SQL error).
Expecting ORDER BY inside a subquery to guarantee outer query order.
Omitting ORDER BY inside a window function when the function depends on row sequence.
Using column position numbers in ORDER BY and breaking queries after schema changes.

First Introduced In

ANSI SQL-86

Frequently Asked Questions

What does BY do in SQL?

BY pairs with GROUP, ORDER, or PARTITION to define how rows are grouped, sorted, or partitioned.

Is BY required in every SELECT statement?

No. Use BY only when you need GROUP BY, ORDER BY, or PARTITION BY. Simple selects that do not aggregate or sort do not need it.

Does GROUP BY automatically sort the data?

Not necessarily. Some engines may output grouped rows in sorted order as a side effect, but the SQL standard does not guarantee this. Always add ORDER BY if order is essential.

Why do I get a "column must appear in the GROUP BY" error?

All nonaggregated columns in the SELECT list must be present in the GROUP BY clause, or be functionally dependent on the grouped columns. Add the column to GROUP BY or wrap it in an aggregate function.