CODEWITHSUNDEEP
sqlsql-server
master2080
master2080

Reputation: 361

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.

Upvotes: 0

Views: 82

Answers (2)

iamdave
iamdave

Reputation: 12243

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)  -- Rank the data within the same year as this data row.
                                order by sum(s.price) desc   -- Order by the sum total of sales price, with the largest first (Descending).  This means that rank 1 is the highest amount.
                                ) 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.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

First, never use commas in the FROM clause. Always use explicit JOIN syntax.

The answer to your question is to use window functions. If there is a tie and you wand all values, then RANK() or DENSE_RANK(). If you always want exactly one -- even if there are ties -- then ROW_NUMBER().

select ss.*
from (select year(s.sale_date) as yyyy, sm.First_Name, sum(s.price) as total_price,
             row_number() over (partition by year(s.sale_date)
                                order by sum(s.price) desc
                               ) as seqnum
      from Salesman sm join
           Sale s
           on sm.ID = s.Salesman_ID 
      group by year(s.sale_date), sm.First_Name
     ) ss
where seqnum = 1;

Note that the Buyers table is unnecessary for this query.

Upvotes: 1

Related Questions