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.
Code language: SQL (Structured Query Language) (sql)
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
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.