SELECT ItemView.Name AS ItemName,ItemView.Country AS Country,ItemView.Color AS Color,ItemView.SlabType AS SlabType,ItemView.PattName AS PattName,ItemView.ItemImagePath AS ItemImagePath,ItemView.Seq AS Seq,ItemView.ItemID AS SKU,ItemView.SecondaryColor AS SecondaryColor,ItemView.ColorTone AS ColorTone,ItemView.Pattern AS Pattern,ItemView.Collection AS Collection,ItemView.Classification AS Classification,ListPrices.Price1 AS LP,ListPrices.Price2 AS FP,ListPrices.Price3 AS BP,ListPrices.Price4 AS DP,SlabView.Thickness AS Thickness,Item.SubCategoryValue AS SubCategoryValue,Item.IsNewArival AS IsNewArival,SalePrice.SlabLength AS SlabLength,SalePrice.SlabWidth AS SlabWidth,SalePrice.IDTwo AS IDTwo,SalePrice.LocationName AS LocationName,min(SalePrice.InventoryStatus) AS InventoryStatus,SalePrice.Slabs AS Slabs, SUM(CASE WHEN SalePrice.InventoryStatus = 'INSTOCK' THEN 1 ELSE 0 END) as AvailableSlabs, SalePrice.SeqNo AS SeqNo,SalePrice.SaleLP AS SaleLP,SalePrice.SaleFP AS SaleFP,SalePrice.SaleBP AS SaleBP,SalePrice.SaleDP AS SaleDP,SalePrice.ImagePath AS ImagePath,SalePrice.Bin AS BIN, CASE WHEN SalePrice.SaleFP > 0 THEN MIN(SalePrice.SaleFP) ELSE MAX(ListPrices.Price2) END AS FinalPrice, (select tr.SlabCount from Custom_TrendingStones tr where ItemView.ItemID = tr.SKU) AS SlabCount, Item.SubItemType, Item.ItemGroupValue, (SELECT Available FROM Hold_Custom_AvailableSlabs as a WHERE a.SKU = Item.SKU) as Available from ((((Custom_ItemView AS ItemView left join Custom_SalePrice AS SalePrice on(ItemView.ItemID = SalePrice.SKU)) left join Item AS Item on(ItemView.ItemID = Item.SKU)) left join Custom_SlabView AS SlabView on(ItemView.ItemID = SlabView.ItemID)) left join R_ListPrices AS ListPrices on(ItemView.ItemID = ListPrices.SKU)) where (ItemView.ItemID like 'SMQ%' or ItemView.ItemID like 'SMM%' or ItemView.ItemID like 'NM%' or ItemView.ItemID like 'NG%' or ItemView.ItemID like 'NQ%' or ItemView.ItemID like 'VDQ%' or ItemView.ItemID like 'NTG%' or ItemView.ItemID like 'NTQ%' or ItemView.ItemID like 'NTM%' or ItemView.ItemID like 'LSS%') and ItemView.ItemID in (select tr.SKU from Custom_TrendingStones tr where ItemView.ItemID = tr.SKU) group by ItemView.ItemID,SalePrice.SlabLength,SalePrice.SlabWidth,SalePrice.IDTwo, Item.SubCategoryValue order by (select tr.SlabCount from Custom_TrendingStones tr where ItemView.ItemID = tr.SKU) desc;