CODEWITHSUNDEEP
sqlsql-server
WadeB
WadeB

Reputation: 89

Counting instances and displaying in new column with SQL

Having a problem getting this query to work. I can get it all except for the Count column. I know its simple but there's something I'm just not getting here. Please see problem below.

List the first name, middle name and last name from the Employees table, and the shipping city and their total number or count from the Orders table. Only display the shipping city if the count of the shipping city is greater than or equal to 7. Display the first name of the employee followed by a space followed by the middle name followed by a space followed by the last name but do not leave a space if the employee does not have a middle name. Order the result set by the employee name. The query should produce the result set listed below.

enter image description here

SELECT        CONCAT(Employees.FirstName,' ',Employees.MiddleName, ' ', Employees.LastName) AS Name,
              Orders.ShipCity
FROM          Employees INNER JOIN
              Orders ON Employees.EmployeeID = Orders.EmployeeID

Thanks!

Upvotes: 0

Views: 38

Answers (2)

Chamika Goonetilaka
Chamika Goonetilaka

Reputation: 716

This would do :-

SELECT        CONCAT(Employees.FirstName,' ',Employees.MiddleName, ' ', Employees.LastName) AS Name,
              Orders.ShipCity,
              COUNT(1) [Count]
FROM          Employees INNER JOIN
              Orders ON Employees.EmployeeID = Orders.EmployeeID
GROUP BY Employees.FirstName, Employees.MiddleName, Employees.LastName, Orders.ShipCity

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521997

Use COUNT as an analytic function:

SELECT
    CONCAT(e.FirstName, ' ', e.MiddleName, ' ', e.LastName) AS Name,
    o.ShipCity,
    COUNT(*) OVER (PARTITION BY e.EmployeeID) [Count]
FROM Employees e
INNER JOIN Orders o
    ON e.EmployeeID = o.EmployeeID;

Upvotes: 0

Related Questions