master2080 master2080 - 10 days ago 5
SQL Question

How do I proceed on this query

I want to know if there's a way to display more than one column on an aggregate result but without it affecting the group by.

I need to display the name alongside an aggregate result, but I have no idea what I am missing here.

This is the data I'm working with:

enter image description here

It is the result of the following query:

select * from Salesman, Sale,Buyer
where Salesman.ID = Buyer.Salesman_ID and Buyer.ID = sale.Buyer_ID


I need to find the salesman that sold the most stuff (total price) for a specific year.

This is what I have so far:

select DATEPART(year,sale.sale_date)'year', Salesman.First_Name,sum(sale.price)
from Salesman, Sale,Buyer
where Salesman.ID = Buyer.Salesman_ID and Buyer.ID = sale.Buyer_ID
group by DATEPART(year,sale.sale_date),Salesman.First_Name


This returns me the total sales made by each salesman.

enter image description here

How do I continue from here to get the top salesman of each year?

Maybe the query I am doing is completely wrong and there is a better way?

Any advice would be helpful.

Thanks.

Answer

This should work for you:

select *
from(
    select DATEPART(year,s.sale_date) as SalesYear  -- Avoid reserved words for object names
            ,sm.First_Name
            ,sum(s.price) as TotalSales
            ,row_number() over (partition by DATEPART(year,s.sale_date)
                                order by sum(s.price) desc
                                ) as SalesRank    -- Orders your salesmen by the total sales within each year, with 1 as the best
    from Buyer b
        inner join Sale s
            on(b.ID = s.Buyer_ID)
        inner join Salesman sm
            on(sm.ID = b.Salesman_ID)
    group by  DATEPART(year,s.sale_date)
                ,sm.First_Name
    ) a
where SalesRank = 1    -- This means you only get the top salesman for each year.
Comments