SQL query

SQL – Concatenate Names and get totals

In this short blog post, I’ll show you how to concatenate an employee firstname and last name, then get the total orders made under that employee.

I’m using Northwind database available from Microsoft. It’s a free sample database. You can replicate the same set of results as shown below.

USE [Northwind] SELECT emp.EmployeeID ,CONCAT(emp.FirstName, ' ' , emp.LastName) as EmployeeName ,ord.Total FROM [Employees] as emp LEFT JOIN (SELECT COUNT(*) as Total, EmployeeID FROM [Orders] GROUP BY EmployeeID) as ord ON emp.EmployeeID = ord.EmployeeID ORDER BY emp.LastName
Code language: SQL (Structured Query Language) (sql)

In the first column, it shows the EmployeeID.
In the second column, the CONCAT function wraps the firstname, a whitespace and a lastname, making up the fullname of the employee.
In the third column, it lists the orders total. This results comes from the LEFT JOIN query further in the SQL.

The LEFT JOIN shows it counts all orders and that column is named as Total, it then lists the EmployeeID. It’s grouped by EmployeeID. The LEFT JOIN query is wrapped and named as ord. It’s joined up with the EmployeeID from the Employees table.

The whole dataset is sorted by LastName. You can see the results below.

EmployeeIDEmployeeNameTotal
5Steven Buchanan42
8Laura Callahan104
1Nancy Davolio123
9Anne Dodsworth43
2Andrew Fuller96
7Robert King72
3Janet Leverling127
4Margaret Peacock156
6Michael Suyama67