SQL query

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.

EmployeeID	EmployeeName	Total
5	Steven Buchanan	42
8	Laura Callahan	104
1	Nancy Davolio	123
9	Anne Dodsworth	43
2	Andrew Fuller	96
7	Robert King	72
3	Janet Leverling	127
4	Margaret Peacock	156
6	Michael Suyama	67