Mak

• Aug 29th, 2007

 

First, you have to ask two questions -

Assuming the given table is (sp, item, Q)   ---  sp is sales person id, Q is quantity sold for that item.

a) are there duplicate rows, which means we will need to sum the quantity first.
b) what happens if a sales person sold more than one item with the SAME maximum quantity ? ie. John's top sale was 500, however, he sold 500 computers and also sold 500 laptops. Does the report show only one item, or, both items, and, if only one, then which one should be shown (ie. what is the criteria). I am assuming that both items should be shown.  

Here is the query

select  sp, item, sum(Q) as TQ
from <table>
into #t
group by sp, item

select sp, item, TQ
from #t a
where a.TQ = (select max(TQ) from #t b where b.sp = a.sp)

========

if you want everything in one query, then here is the one --



select a.sp, a.item, a.TQ
from  (select  sp, item, sum(Q) as TQ
from <table>
group by sp, item ) a
where a.TQ = (select max(TQ) from #t b where b.sp = a.sp)

========

swadheengupta

 Profile Answers by swadheengupta Questions by swadheengupta

• Sep 8th, 2008

 

Assuming that both the items are required to be shown if two or more items are sold of same quantity. The query is :-

select sales_man,item,sales from table where (sales_man,sales) in 
(select sales_man,max(sales) from table group by sales_man);

nagaraju_p

 Profile Answers by nagaraju_p 

• Sep 9th, 2009

 

select a.sales_person, a.item, a.TotalQuantity
FROM (select b.sales_person,b.item,SUM(b.InvAmount ) as TotalQuantity from sales_data b
Group by b.sales_person,b.item)as a
GROUP BY a.sales_person
HAVING a.TotalQuantity  = MAX(a.TotalQuantity )