CODEWITHSUNDEEP
sqlsql-serversql-server-2012
FailedUnitTest
FailedUnitTest

Reputation: 1800

Column invalid in the select list, not contaned in aggregate function

I would like to display columns like this: 

ProductID | Price | PricingPlanLicense | PricingPlanQuality
----------+-------+--------------------+-------------------    
1            16.99         Buy                 HD
1            12.99         Buy                 SD
1             2.99         Rent                HD
3             4.99         Rent                HD

The problem is that in my data I have multiple PricingPlanLicense and PricingPlanQuality combinations and I would like to take the one with the highest price.

So far I have this query: 

SELECT 
    ProductID, MAX(Price), PricingPlanLicense, PricingPlanQuality
FROM 
    [#ExportPricingTable] 
GROUP BY 
    PricingPlanLicense, PricingPlanQuality

But this doesn't work because ProductID is not aggregated, how can I tell SQL Server to use the ProductID that is already there?

Thanks

Upvotes: 0

Views: 118

Answers (4)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521492

A canonical way of doing this to use a join to obtain the ProductID corresponding to each group which has the maximum price:

SELECT t1.ProductID, t1.Price, t1.PricingPlanLicense, t1.PricingPlanQuality
FROM [#ExportPricingTable] t1
INNER JOIN
(
    SELECT MAX(Price) AS maxPrice, PricingPlanLicense, PricingPlanQuality
    FROM [#ExportPricingTable] 
    GROUP BY PricingPlanLicense, PricingPlanQuality
) t2
    ON t1.Price = t2.maxPrice AND t1.PricingPlanLicense = t2.PricingPlanLicense AND
       t1.PricingPlanQuality = t2.PricingPlanQuality

Here is a demo you can try:

SQLFiddle

Upvotes: 1

Michael Z.
Michael Z.

Reputation: 1473

Add ProductID to the Group By clause.

SELECT ProductID, MAX(Price), PricingPlanLicense, PricingPlanQuality
FROM [#ExportPricingTable] 
GROUP BY ProductID, PricingPlanLicense, PricingPlanQuality

Upvotes: 1

msheikh25
msheikh25

Reputation: 578

When you want to select more columns than your group for an aggregate, you can use row_number() to order the data then select first row:

SELECT * FROM (
  SELECT ProductID, Price, PricingPlanLicense, PricingPlanQuality
         ROW_NUMBER() OVER (partition by PricingPlanLicense, PricingPlanQuality ORDER BY Price desc) RN
  FROM [#ExportPricingTable] 
)
WHERE RN = 1

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can pick one ID, using either MIN or MAX, like:

SELECT MIN(ProductID), MAX(Price), PricingPlanLicense, PricingPlanQuality
FROM [#ExportPricingTable] 
GROUP BY PricingPlanLicense, PricingPlanQuality

This will select the minimum ProductID value for every PricingPlanLicense, PricingPlanQuality group of values.

Upvotes: 0

Related Questions