SQL DISTINCT and Aliases

DISTINCT removes duplicate rows from a result, and AS gives a column or table a temporary name. Both are small, but you'll use them in almost every report.

Syntax

SELECT DISTINCT column1, column2
FROM table_name;

SELECT column1 AS new_name
FROM table_name AS t;

DISTINCT examples

1. Unique values in one column

Which cities do ShopKart customers come from?

SELECT DISTINCT city
FROM customers
ORDER BY city;

Result:

(11 rows)

There are 12 customers but only 11 cities, because two customers live in Bengaluru.

2. Unique combinations of columns

With two columns, DISTINCT keeps each unique pair:

SELECT DISTINCT status, payment_method
FROM orders
ORDER BY status, payment_method;

Result:

(12 rows)

3. Count distinct values

COUNT(DISTINCT ...) counts unique values. How many different customers have placed an order?

SELECT COUNT(*) AS total_orders,
       COUNT(DISTINCT customer_id) AS unique_customers
FROM orders;

Result:

(1 row)

24 orders came from 11 customers. This "orders vs. unique customers" pattern shows up constantly in analyst work.

Alias examples

4. Column aliases

Aliases make calculated columns readable:

SELECT product_name AS product,
       price - cost_price AS margin,
       ROUND((price - cost_price) / price * 100, 1) AS margin_pct
FROM products
LIMIT 4;

Result:

(4 rows)

If an alias contains spaces or capitals you want to keep, wrap it in double quotes: AS "Margin %".

5. Table aliases

Table aliases are short names for tables. They save typing and become essential once you join tables:

SELECT p.product_name, p.brand
FROM products AS p
LIMIT 3;

Result:

(3 rows)

Common mistakes

Using a column alias in WHERE. The alias doesn't exist yet when WHERE runs:

SELECT product_name, price - cost_price AS margin
FROM products
WHERE margin > 3000;

Error: column "margin" does not exist

Repeat the expression instead:

SELECT product_name, price - cost_price AS margin
FROM products
WHERE price - cost_price > 3000;

Result:

(3 rows)

• Using DISTINCT to hide a bad join. If a join creates duplicates, fix the join. DISTINCT just hides the symptom and can make totals wrong.
• Putting DISTINCT in the wrong place. It goes right after SELECT, not before a single column in the middle of the list.

Practice question

List every unique brand and category_id combination in products, sorted by category_id.

SELECT DISTINCT category_id, brand
FROM products
ORDER BY category_id, brand;

Key takeaways

• DISTINCT removes duplicate rows across all selected columns.
• COUNT(DISTINCT col) counts unique values.
• AS renames columns and tables in the output only.
• Column aliases work in ORDER BY but not in WHERE.