Column Aliases in SQL: Renaming Columns with AS

Rename columns and expressions in your query results using AS so your output is clean, readable, and ready to share.

You pull a revenue report and the output column is labelled SUM(o.total_amount). That is not going to make sense to a stakeholder. You join two tables and both have a column called status, so the output has two columns with the same name. Aliases solve both of these problems immediately and cleanly.

Basic Column Alias Syntax

The syntax is straightforward. Write the column name, then AS, then the alias you want.

-- Rename columns for a cleaner customer report
SELECT
    c.customer_id   AS id,
    c.first_name    AS first_name,
    c.last_name     AS last_name,
    c.email         AS contact_email,
    c.loyalty_tier  AS tier
FROM customers c;

-- Rename product columns for a catalogue output
SELECT
    p.product_name  AS name,
    p.price         AS unit_price,
    p.stock_qty     AS stock,
    p.brand         AS brand_name
FROM products p
WHERE p.is_active = 1;

The alias only exists in the query result. The underlying table and column names stay exactly as they are.

Aliases on Computed Columns and Expressions

Aliases are most useful when you have a calculated column that has no natural name. Without an alias, the column header shows the raw expression, which is difficult to read in any report or export.

-- Give computed columns meaningful names
SELECT
    p.product_name,
    p.price,
    p.cost_price,
    p.price - p.cost_price                          AS gross_profit,
    ROUND((p.price - p.cost_price) / p.price * 100, 2) AS margin_pct
FROM products p
WHERE p.price > 0;

-- Full name from two columns, labelled clearly
SELECT
    c.customer_id,
    c.first_name || ' ' || c.last_name  AS full_name,
    c.email,
    c.created_at                        AS registered_at
FROM customers c;

-- Shipping delay in days with a clear label
SELECT
    o.order_id,
    o.order_date,
    o.shipped_date,
    ROUND(JULIANDAY(o.shipped_date) - JULIANDAY(o.order_date), 1) AS days_to_ship
FROM orders o
WHERE o.shipped_date IS NOT NULL
LIMIT 10;

Key idea: Aliases on computed columns are not optional. Without them, the column has no name in most tools and the expression itself becomes the header. Always name every calculated column with AS.

Aliases on Aggregate Functions

Any time you use COUNT(), SUM(), AVG(), MIN(), or MAX(), give the result a clear alias. The default label is the function call itself, which is not usable in a report.

-- Aggregate report with named output columns
SELECT
    o.status,
    COUNT(o.order_id)         AS total_orders,
    ROUND(SUM(o.total_amount), 2) AS total_revenue,
    ROUND(AVG(o.total_amount), 2) AS avg_order_value,
    MIN(o.total_amount)       AS smallest_order,
    MAX(o.total_amount)       AS largest_order
FROM orders o
GROUP BY o.status
ORDER BY total_revenue DESC;

-- Payment summary by method
SELECT
    pay.method,
    COUNT(*)                      AS payment_count,
    ROUND(SUM(pay.amount), 2)     AS total_collected,
    ROUND(AVG(pay.amount), 2)     AS avg_payment
FROM payments pay
WHERE pay.status = 'Completed'
GROUP BY pay.method
ORDER BY total_collected DESC;

Using Aliases in ORDER BY

Aliases assigned in SELECT can be used in ORDER BY because ORDER BY runs after SELECT. This avoids repeating a long expression.

-- Reference the alias in ORDER BY instead of repeating the expression
SELECT
    p.product_name,
    ROUND((p.price - p.cost_price) / p.price * 100, 2) AS margin_pct
FROM products p
WHERE p.price > 0
ORDER BY margin_pct DESC
LIMIT 10;

-- Sort by the computed shipping delay
SELECT
    o.order_id,
    o.order_date,
    ROUND(JULIANDAY(o.shipped_date) - JULIANDAY(o.order_date), 1) AS days_to_ship
FROM orders o
WHERE o.shipped_date IS NOT NULL
ORDER BY days_to_ship DESC
LIMIT 10;

Aliases with Spaces

If your alias needs to include spaces, wrap it in double quotes. This is common when producing output for non-technical stakeholders.

SELECT
    c.first_name || ' ' || c.last_name  AS "Customer Name",
    c.email                             AS "Email Address",
    c.loyalty_tier                      AS "Loyalty Tier",
    c.country                           AS "Country"
FROM customers c
ORDER BY c.last_name;

For most internal queries, underscores are simpler and safer. Reserve quoted aliases for final report exports.

Common Mistakes

-- wrong
SELECT o.total_amount AS revenue
FROM orders o
WHERE revenue > 500;

-- correct
SELECT o.total_amount AS revenue
FROM orders o
WHERE o.total_amount > 500;

WHERE runs before SELECT, so the alias revenue does not exist yet at the filtering step. Use the original column expression in WHERE.

---

-- wrong
SELECT
    c.first_name AS name,
    c.last_name AS name
FROM customers c;

-- correct
SELECT
    c.first_name AS first_name,
    c.last_name  AS last_name
FROM customers c;

Two columns with the same alias create ambiguity in the result set. Some tools will show both, others will only show one. Always use distinct aliases for each column.

---

-- wrong
SELECT COUNT(*) total_orders
GROUP BY o.status
FROM orders o;

-- correct
SELECT o.status, COUNT(*) AS total_orders
FROM orders o
GROUP BY o.status;

Clause order matters. FROM must come before GROUP BY, and AS belongs in the SELECT list alongside the expression it is naming.

Quick Recap

Use AS to give any column or expression a readable label in your query output. The alias does not affect the underlying table in any way.
Always alias computed columns and aggregate functions. Without an alias, their output headers are the raw expressions, which are unreadable in reports.
Aliases can be referenced in ORDER BY but not in WHERE, HAVING, or GROUP BY, because those clauses run before SELECT assigns the alias.

Exercise 1 Easy

Write a query that returns product_name, price, and cost_price from the products table. Alias price as selling_price and cost_price as purchase_price. Only include active products. Sort by selling_price descending.

Hint: Use AS after each column you want to rename. Filter with WHERE p.is_active = 1.

Solution

SELECT p.product_name, p.price AS selling_price, p.cost_price AS purchase_price FROM products p WHERE p.is_active = 1 ORDER BY selling_price DESC;

Exercise 2 Medium

Write a query that returns each employee's full name as full_name using first and last name concatenation, their role, department, and their annual salary labelled annual_salary. Also compute a monthly salary as monthly_salary by dividing salary by 12, rounded to 2 decimal places. Sort by annual_salary descending.

Hint: Use || for concatenation and alias the result. Compute ROUND(e.salary / 12, 2) for monthly salary. Both computed columns need aliases.

Solution

SELECT e.first_name || ' ' || e.last_name AS full_name, e.role, e.department, e.salary AS annual_salary, ROUND(e.salary / 12, 2) AS monthly_salary FROM employees e ORDER BY annual_salary DESC;

Exercise 3 Hard

Write a query that shows each customer's customer_id, full name as full_name, total number of orders as order_count, and total amount spent as total_spent rounded to 2 decimal places. Only include customers with more than 2 orders. Join customers and orders. Sort by total_spent descending. Limit to 10 rows.

Hint: Join on customer_id. Use COUNT(o.order_id) aliased as order_count and ROUND(SUM(o.total_amount), 2) aliased as total_spent. Use HAVING COUNT(o.order_id) > 2.

Solution

SELECT c.customer_id, c.first_name || ' ' || c.last_name AS full_name, COUNT(o.order_id) AS order_count, ROUND(SUM(o.total_amount), 2) AS total_spent FROM customers c JOIN orders o ON c.customer_id = o.customer_id GROUP BY c.customer_id, c.first_name, c.last_name HAVING COUNT(o.order_id) > 2 ORDER BY total_spent DESC LIMIT 10;

Frequently Asked Questions

How do you rename a column in SQL query results?

Use the AS keyword after the column name or expression, like SELECT first_name AS name. The alias only affects the output label and does not modify the actual table.

Is the AS keyword required for a column alias in SQL?

No. You can write the alias directly after the column name without AS, like SELECT first_name name. But using AS makes the intent explicit and is easier to read.

Can you use a column alias in a WHERE clause?

No. Aliases are assigned in the SELECT step, which runs after WHERE. You cannot reference an alias in WHERE, GROUP BY, or HAVING unless the database supports it specifically. Use the full expression instead.

Can a column alias contain spaces in SQL?

Yes, but you need to wrap it in double quotes, like SELECT total_amount AS "Total Amount". For simplicity, most analysts use underscores instead.