SQL Server: Combining employee names and order counts from separate tables?

Data and Databases
1

I’m stuck with a SQL problem. I’ve got two tables: Sales and Staff. Both have worker IDs. I want to show the total sales for each worker, but with their full name instead of just an ID.

Right now, I can get the worker ID and their total sales, but I can’t figure out how to add their full name from the Staff table. I’ve tried using CAST and CONVERT to match Sales.WorkerID with Staff.WorkerID, but no luck.

Here’s what I have so far:

SELECT 
    s.WorkerID AS Salesperson,
    COUNT(SaleID) AS TotalSales
FROM
    Sales s
JOIN 
    Staff st ON st.WorkerID = s.WorkerID
GROUP BY 
    s.WorkerID;

This gives me:

|WorkerID|TotalSales|
+--------+----------+
|5       |87        |

But I want:

|FullName    |TotalSales|
+------------+----------+
|Jane Smith  |87        |

Any ideas on how to make this work? Thanks!

2

hey there! have you considered using a subquery? something like:

SELECT
(SELECT FirstName + ’ ’ + LastName FROM Staff WHERE WorkerID = s.WorkerID) AS FullName,
COUNT(*) AS TotalSales
FROM Sales s
GROUP BY s.WorkerID

maybe this could work? what do you think about it? :thinking:

3

hey, you can try this:

SELECT
st.FirstName + ’ ’ + st.LastName AS FullName,
COUNT(s.SaleID) AS TotalSales
FROM Sales s
INNER JOIN Staff st ON st.WorkerID = s.WorkerID
GROUP BY st.FirstName, st.LastName

this should give you what u want. Good luck!

4

To achieve what you’re looking for, you need to modify your SELECT statement to include the full name from the Staff table. Here’s an improved version of your query:

SELECT 
    CONCAT(st.FirstName, ' ', st.LastName) AS FullName,
    COUNT(s.SaleID) AS TotalSales
FROM
    Sales s
JOIN 
    Staff st ON st.WorkerID = s.WorkerID
GROUP BY 
    st.FirstName, st.LastName;

This query assumes your Staff table has FirstName and LastName columns. The CONCAT function combines these into a full name. By joining the tables and selecting the name fields from Staff, you’ll get the desired output. Remember to adjust column names if they’re different in your actual tables. This approach should solve your problem efficiently.