How to find out the maximum sales of an item for each sales man. The table consists sales_man, item, sales columns, each sales man will have the different sales values for different items. here I have to find out for every sales man, which item he sold maximum
RE: How to find out the maximum sales of an item for e...
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)
RE: How to find out the maximum sales of an item for each sales man.The table consists sales_man, item, sales columns, each sales man will have the different sales values for different items.here I have to find out for every sales man, which item he sold
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);
RE: How to find out the maximum sales of an item for each sales man.The table consists sales_man, item, sales columns, each sales man will have the different sales values for different items.here I have to find out for every sales man, which item he sold
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 )
