I working on 2 tables in a trial database stock and sales, I am trying to find the stock item(s) that have had the least number of sales.

The sales table has the following fields: sales\_id, transaction\_id, stock\_id, amount
The stock table has : sid, upc, name, description, amount, price
when I run the following SQL:

```
  SELECT st.name,
    COUNT( s.stock_id ) AS num_of_individual_sales
    FROM sales AS s
    JOIN stock as st
    ON st.sid = s.stock_id
    GROUP BY s.stock_id

```

I get:

```
banana  7
mars bar    4
fiery hot monster munch 2
WWII hand grenade   4
cheese toastie  2
fish paste  3
belly fluff 2
Unicorn tears   2
spag sauce  4
cooking oil 2

```

So I then try and wrap it up as a sub query:

```
SELECT ans.name, MIN(ans.num_of_individual_sales)
FROM
(

SELECT st.name,
COUNT( s.stock_id ) AS num_of_individual_sales
FROM sales AS s
JOIN stock as st
ON st.sid = s.stock_id
GROUP BY s.stock_id

) AS ans

```

The result I'm getting is 

banana 2

which even with my non-functioning brain is wrong, I was expecting to get a a recordset with all the 2's (monstermuch, cheese toastie etc)

I have tried loads of different combos but this seems to give the nearest result just not the one I want...

where am I going wrong?

cheers

Zen

**UPDATE**
worked out at about the same time as Bill Karwin posted that my understanding of the MIN and MAX commands was inaccurate.

I already understood the idea of using an ORDER BY ASC and then I could have used PHP to cut off the data when the COUNT value changed but I'm kind of doing this as a learning exercise so although there may be an easier route I was hoping that it was still possible to do it via the MIN command, I think I need to do a double sub query but have got lost somewhere, this is the newest variant.

```
SELECT st.name, st.price,
s.sales_id
From stock AS st
JOIN sales AS s
ON  st.sid = s.stock_id
WHERE COUNT(s.stock_id)=(

SELECT MIN(ans.num_of_individual_sales)
FROM
(

SELECT st.name,
COUNT( s.stock_id ) AS num_of_individual_sales
FROM sales AS s
JOIN stock as st
ON st.sid = s.stock_id
GROUP BY s.stock_id

) AS ans
)GROUP BY s.stock_id

```

but I am getting an invalid group function error