Search code examples
sqlsql-serveradventureworks

Adventure Works SQL Server 2014 Enquiry

I am writing a SQL query using the AdventureWorks 2014 database. I want to show which employee has sold for the highest order value.

I tried to write each select statement by itself (see below), but I'd like to be able to combine both queries into one:

select 
    s.SalesOrderID, s.SalesPersonID, COUNT(sd.SalesOrderID) as count 
from 
    Sales.SalesOrderHeader s, Sales.SalesOrderDetail sd
where 
    s.SalesOrderID = sd.SalesOrderID 
group by           
    sd.SalesOrderID, s.SalesOrderID, s.SalesPersonID 
order by 
    sd.SalesOrderID

select 
    sd.SalesOrderID, sd.LineTotal, count (sd.SalesOrderID) as count 
from 
    Sales.SalesOrderDetail sd
group by 
    sd.SalesOrderID, sd.LineTotal 
order by 
    sd.SalesOrderID

enter image description here

Solution

  • are you looking for something like this:

    select top 1  
    s.SalesPersonID
    ,sum(sd.LineTotal ) as orderTotal
    s.salesorderid
from
    Sales.SalesOrderHeader s
    inner join  Sales.SalesOrderDetail sd
        on s.SalesOrderID = sd.SalesOrerID
group by 
    s.SalesPersonID
    s.salesorderid
order by
    orderTotal desc

    in SQL server you can just ask for a limited number of rows with the top function (this can give you the highest order value when sorted correctly). this can be used with a group by that adds all the line totals together that have the same values in the columns being grouped by.