Get Mode Average? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Administration

Get Mode Average?

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2014-04-22 : 12:18:41

This has been inexplicably hard to figure out... I have grouped together my sales into a query & it returns results similar to the table below. It has the SKU, Sale price grouped & the amount of instances of that group.

I want to select only the SKU & Sale price for the instance with the maximum value... It seems easy enough, but every time I add something to the query or sub query, I violate one rule or another.

Declare @Temp Table(
	Id Int Identity(1,1)
	, Sku nvarchar(500)
	, SalePrice decimal(18,2)
	, [Instances] [int] 
	)
Insert into @Temp Values('ABCD','11.29',5)
Insert into @Temp Values('ABCD','11.97',2)
Insert into @Temp Values('ABCD','11.49',3)
Insert into @Temp Values('ASDFCD','17.09',5)
Insert into @Temp Values('ASDFCD','16.99',4)
Insert into @Temp Values('ESRTG','17.49',8)
Insert into @Temp Values('ESRTG','17.99',2)
Insert into @Temp Values('ESRTG','18.89',44)
Insert into @Temp Values('ESRTG','18.95',4)

Select SKU, SalePrice, Instances
From   @Temp

-Sergio
I use Microsoft SQL 2008

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-04-22 : 16:24:40

If there is a tie for the max instances value which do you want - or do you want both? If you want all ties then use the "rank" code below. If you want just one but want to break ties by the salePrice then append ", SalePrice desc" to the row_number() order by clause.


select sku, salePrice, Instances
from   (
       Select SKU
              , SalePrice
              , Instances
              , row_number() over (partition by SKU order by Instances desc) as rn
              --, rank() over (partition by SKU order by Instances desc) as rnk
       From   @Temp t
       ) d
where rn = 1
--where rnk = 1

Be One with the Optimizer
TG

SergioM
Posting Yak Master

170 Posts

Posted - 2014-04-22 : 17:12:10

Thanks!

Looks like 'partition by' is a useful command. I've hit a few dead ends where that's the solution

-Sergio
I use Microsoft SQL 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources