jeudi 30 juin 2016

SQL - Display the minimum value of an average price

My database contains a list of products and finishes. I have been able to successfully average the price of each finish option, but I only want to display the lowest priced option.

Here is a sample of my data.

Table 1 - product_t

ProductID   ProductLineID   ProductDescription  ProductFinish   ProductStandardPrice    ProductOnHand
1   1   "Cherry End Table"  Cherry  175.00  0
2   1   "Birch Coffee Tables"   Birch   200.00  0
3   1   "Oak Computer Desk" Oak 750.00  0
4   1   "Entertainment Center"  Cherry  1650.00 0

This query results in a list of each available product finish and the average price of an item that has the finish.

SELECT ProductFinish, AVG(ProductStandardPrice) as AveragePrice
FROM product_t
WHERE ProductFinish IS NOT NULL
GROUP BY ProductFinish;

However, I only want to display the lowest price that results from this query. I attempted this query, but it will not execute.

 SELECT ProductFinish, MIN (AVG(ProductStandardPrice)) as AveragePrice
 FROM product_t
 WHERE ProductFinish IS NOT NULL
 GROUP BY ProductFinish;

Thanks in advance for any help. It is much appreciated.

Aucun commentaire:

Enregistrer un commentaire