Group by and count with inner join

I have a table with orders and a table with material (1:N). I want to know how many orders have screens in the material table for a certain production order and country.

开发者_StackOverflow

The query at the bottom gives me the quantity of screens per order for each order instead of the expected result 5:

6
1 
6
2
2 

SELECT Count(ORD.ORDERNUMMER)
FROM        Orders AS ORD
INNER JOIN Material AS KP ON KP.Ordernummer = ORD.Ordernummer
WHERE (KP.SOORT = 'SCREEN') AND ORD.PRODUCTIEORDER = '201132' AND ORD.LAND = 'Belgie'
Group By ORD.Ordernummer HAVING (COUNT(*) >= 1)

I just want to have 5 returned.

Thanks in advance,

Mike

---

You need to count each distinct order, if it has at least one line of material which is screen.

SELECT Count(DISTINCT ORD.ORDERNUMMER)
FROM        Orders AS ORD
INNER JOIN Material AS KP ON KP.Ordernummer = ORD.Ordernummer
WHERE (KP.SOORT = 'SCREEN') AND ORD.PRODUCTIEORDER = '201132' AND ORD.LAND = 'Belgie'

---

Don't group the results if you need the whole total!

SELECT Count(ORD.ORDERNUMMER) as totalScreens
FROM Orders AS ORD
INNER JOIN Material AS KP ON KP.Ordernummer = ORD.Ordernummer
WHERE (KP.SOORT = 'SCREEN') AND ORD.PRODUCTIEORDER = '201132' AND ORD.LAND = 'Belgie'
HAVING (totalScreens >= 1)