counting occurance in a inner join query

[FNBIT]

Hello,
It's been a long time since I have played with Access. I have a list of purchase orders which shows the part numbers and the price of that part at that time it was sold.

I want a list of the part numbers with the lowest price and then how many times it was sold. Here is the query I have that gives me the list of part numbers and the the lowest price (I think, I need to verify it is the lowest price). However I am having problems getting it to show me the totals for each part number. The query below does not include the count of times the item was sold, just the PN and lowest price.

SELECT T.PN, T.UnitPrice
FROM [PO-List] as T INNER JOIN
( SELECT PN, Min(UnitPrice) as LowPrice
FROM [PO-List]
GROUP BY PN
) as LP
ON T.PN = LP.PN and
T.UnitPrice = LP.LowPrice

How can I add on to this how many times this part occured in the database and sort it by the most sold?

Therefore I would like the following:

PN LowPrice TotalUnitsSold
123 $50 3
456 $75 2
789 $20 2

from a table like this:

ID PN UnitPrice
1 123 $55
2 456 $85
3 123 $50
4 789 $23
5 789 $20
6 456 $75
7 123 $53

Thanks for any help

 

[traingamer]

Try this (tested):

SELECT T.PN, T.UnitPrice[blue], Count(T.PN)[/blue]
FROM [PO-List] T inner Join (Select PN, Min(UnitPrice) as LowPrice
from  [PO-List] group by PN) AS lp
on T.Pn = lp.PN and 
T.UnitPrice = lp.LowPrice

[blue]Group By T.PN, T.UnitPrice[/blue] ;

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard