Question

Please help me with this SQL Statement: Which sales representative has sold the most products? Hint: Name of the sales rep and a number representing the total number of distinct products sold by that rep. This is the number of different products they have sold, not the quantity of any products.

There are two highest values so I cannot do SELECT Top 1.

CUSTOMER_T CustomerID (PK) CustomerName CustomerStreet CustomerCity CustomerState CustomerZip CreditLimit SalesRepID (FK) of EMPLOYEE_T ORDER_T OrderID (PK) CustomerID (FK) of CUSTOMER_T CustomerPONumber OrderDate DueDate ShipDate ORDER_LINE_T OrderID composite (PK), (FK) of ORDER_T ProductID composite (PK), (FK) of PRODUCT_T OrderQuantity PRODUCT_T ProductID (PK) ProductDescription StockQuantity FABRICATED_T ProductID composite (PK), (FK) of PRODUCT_T.ProductID PartID composite(PK), (FK) of PRODUCT_T.ProductID PartQuantity quantity of PartID that goes into ProductID (example, 2 tires In one bicycle) PRICE_HISTORY_T ProductID(FK) StartDate(PK) EndDate UnitPrice PRODUCT_SUPPLIER_T ProducID composite (PK), (FK) of PRODUCT_T SupplierID composite (PK), (FK) of SUPPLIER_T VendorPartID this is the ID the Vendor (i.e., Supplier) uses in their system), similar to CustomerPONumber in the ORDER_t. ProductCost PurchasedQuantity SUPPLIER_T SupplierID (PK) SupplierName SupplierStreet SupplierCity SupplierState SupplierZip EMPLOYEE_T EmployeeID (PK) EmployeeName EmployeeStreet EmployeeCity EmployeeState EmployeeZip ManagerID (FK) unary EMPLOYEE_COURSE_T EmployeeID combination (PK), (FK) of EMPLOYEE_T CourseID combination (PK), (FK) of COURSE_T CompletionDate COURSE_T CourseID (PK) CourseDescription SALES_COMMISSION_RATE_T EmployeeID (PK), (FK) of EMPLOYEE_T CommissionRate

Answer 1

--

Select employeename,countofprod from (

Select E.employeename,count(distinct OK.productid) as CountOfProd from employee_t e inner join customer_t c on e.employeeid =c.salesrepid inner join order_t o on c.customerid=o.customerid inner join order_line_t OK on o.orderid=OK.ordered group by e.employeename) as subtable where countofprod in (

Select max(countofprod) from (

Select E.employeename,count(distinct OK.productid) as CountOfProd from employee_t e inner join customer_t c on e.employeeid =c.salesrepid inner join order_t o on c.customerid=o.customerid inner join order_line_t OK on o.orderid=OK.ordered group by e.employeename) as sub1);

In above query to get distinct product count I have used 2 level of aggression count of distinct to get result. To get sales rep sold max product we are using correlated sub query