CODEWITHSUNDEEP
sqlsql-server-2008mssql-jdbc
AlexeyOrel
AlexeyOrel

Reputation: 13

How to check in which category each employee has sold the most goods? I can display only employee's name and sold goods

The whole table

USE Northwind
SELECT MAX(TotalOrder)


FROM vwEmployesAndMostSoldCategories
GROUP MAX(TotalOrder)

What I am only able to output

USE Northwind
SELECT
   FullName
   , MAX(TotalOrder) AS TheMaxSoldUnits
FROM vwEmployesAndMostSoldCategories
GROUP BY FullName

enter image description here

Upvotes: 1

Views: 118

Answers (2)

GMB
GMB

Reputation: 222582

If I follow you correctly, you can do this using with ties:

select top (1) with ties e.*
from vwEmployesAndMostSoldCategories e
order by rank() over(partition by fullname order by totalorders desc)

For each employee, this returns the row with the greatest totalorders (if there are top ties, all tied rows of the same employee are returned). It doesn't look like you need aggregation here.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521914

You could use a TOP query here:

WITH cte AS (
    SELECT FullName, CatgegoryName,
           SUM(TotalOrder) AS SumTotalOrder,
           ROW_NUMBER() OVER (PARTITION BY FullName
                              ORDER BY SUM(TotalOrder) DESC) rn
    FROM vwEmployesAndMostSoldCategories
    GROUP BY FullName, CategoryName
)

SELECT FullName, CategoryName, SumTotalOrder AS TotalOrder
FROM cte
WHERE rn = 1;

If a given employee might be tied for having two or more categories with the same order total, and you want to show all ties, then replace ROW_NUMBER, with RANK.

Upvotes: 1

Related Questions