Defy Aging Based On Your Age

SELECT top 20 * from ( select min(USID) as idProduct, min(inventory) as inventory, min(USprice) as price, max(groupMax) as maxPrice, min(groupMin) as minPrice, max(USlistprice) as listPrice, min(description) as description, min(prodName) as prodName, min(inventory.USimageUrl) as imageURL, min(inventory.USLargeImageUrl) as largeImageURL, sum(sold365) as sales, min(prodGroupItems.prodGroupID) as prodGroupID, min(gCount.groupCount) as groupCount, min(prodGroups.divider) as divider, min(prodGroups.name) as groupName, sum(count) as count , avg(avgstars) as avgstars, min(US_callOrder) as IH_callOrder, min(US_showPrice) as IH_ShowPrice, min(US_CAllOrderPrice) as callOrderPrice, min(brands.idbrand) as idbrand, min(brands.brandName) as brandName, min(brands.shipsTo) as shipsTo, min(brands.noShipTo) as noShipTo, min(freezes) as freezes, min(aerosol) as aerosol, min(leaks) as leaks, min(podeclined) as podeclined, max(freeGiftID) as freeGiftID, max(USTreshHold) as treshHold, min(USPrescription) as isPrescription FROM [dbo].[inventory] left join brands on brands.idbrand = inventory.idbrand left join prodGroupItems on prodGroupItems.itemID = inventory.itemID left join prodGroups on prodGroupItems.prodGroupID = prodGroups.prodGroupID left join (select prodGroupId, count(prodGroupID) as groupCount, min(USprice) as groupMin, max(USprice) as groupMax from prodGroupItems left join inventory on inventory.itemID = prodGroupItems.itemID Where USActive = 1 group by prodGroupID) as gCount on gCount.prodGroupId = prodGroups.prodGroupID left join (SELECT itemID, COUNT(stars) AS COUNT, round(cast(SUM([stars]) as float)/COUNT([stars]) *2,0)/2 AS avgstars FROM pcReviews WHERE active=1 group by itemID) as reviews on reviews.itemID = inventory.itemID left join freegifts on freeGifts.itemID = inventory.itemID WHERE [inventory].[active]=-1 and USID <>0 and USActive= 1 and brands.USBlock = 0 and inventory.USBlock=0 and brands.USAvail = 1 AND (DESCRIPTION LIKE '%collagen%boosters%' OR SKU = 'collagen boosters' ) AND exists ( SELECT itemID FROM pTypesItems WHERE inventory.itemID = pTypesItems.itemID AND pTypesItems.[active]=1 AND TYPEID = 129 )AND exists ( SELECT itemID FROM pTypesItems WHERE inventory.itemID = pTypesItems.itemID AND pTypesItems.[active]=1 AND TYPEID = 186 )AND exists ( SELECT itemID FROM pTypesItems WHERE inventory.itemID = pTypesItems.itemID AND pTypesItems.[active]=1 AND TYPEID = 4 )AND exists ( SELECT itemID FROM pTypesItems WHERE inventory.itemID = pTypesItems.itemID AND pTypesItems.[active]=1 AND TYPEID = 43 )AND exists ( SELECT itemID FROM pTypesItems WHERE inventory.itemID = pTypesItems.itemID AND pTypesItems.[active]=1 AND TYPEID = 2 )AND exists ( SELECT itemID FROM pTypesItems WHERE inventory.itemID = pTypesItems.itemID AND pTypesItems.[active]=1 AND TYPEID = 169 )AND exists ( SELECT itemID FROM pTypesItems WHERE inventory.itemID = pTypesItems.itemID AND pTypesItems.[active]=1 AND TYPEID = 114 )AND exists ( SELECT itemID FROM pTypesItems WHERE inventory.itemID = pTypesItems.itemID AND pTypesItems.[active]=1 AND TYPEID = 7 )AND exists ( SELECT itemID FROM pAppAreaItems WHERE inventory.itemID = pAppAreaItems.itemID AND pAppAreaItems.[active]=1 AND appAreaID = 1 )AND exists ( SELECT itemID FROM pConcernItems WHERE inventory.itemID = pConcernItems.itemID AND pConcernItems.[active]=1 AND concernID = 6 ) group by CASE When prodGroupItems.prodgroupID is null then cast(newID() as varchar(255)) ELSE cast(prodGroupItems.prodgroupID as varchar(255)) END ) as tempAll Order by SALES DESC, description