SQL Alias

The SQL Alias syntax

The general syntax is:

  1. SELECT columnname AS aliasname
  2. FROM tablename aliasname
  3. WHERE condition

 


 

SQL Alias Examples

Problem: List total customers in each country.
Display results with easy to understand column headers.

  1. SELECT COUNT(C.Id) AS TotalCustomers, C.Country AS Nation
  2. FROM Customer C
  3. GROUP BY C.Country

TotalCustomers and Nation are column aliases.
The table alias (C) in this example is not particularly useful.
Results: 21 records

TotalCustomers Nation
3 Argentina
2 Austria
2 Belgium
9 Brazil
3 Canada

 


Problem: List the total amount ordered by customer
with easy to read column headers

  1. SELECT C.Id AS Identifier, C.LastName + ‘, ‘ + C.FirstName AS CustomerName,
  2. SUM(O.TotalAmount) AS TotalSpent
  3. FROM [Order] O JOIN Customer C ON O.CustomerId = C.Id
  4. GROUP BY C.Id, C.LastName + ‘, ‘ + C.FirstName
  5. ORDER BY TotalSpent DESC

The aliases significantly simplify writing the JOIN and ORDER BY clauses.
The C alias in C.Id helps identify the Customer Id rather then the Order Id.
Results: 89 records

Identifier CustomerName TotalSpent
63 Kloss, Horst 117483.39
71 Pavarotti, Jose 115673.39
20 Mendel, Roland 113236.68
37 McKenna, Patricia 57317.39
65 Wilson, Paula 52245.90

댓글 남기기