Problems with a comlicated select

[Heiko Jörgens]

Hello all together,

my English is not best cause I am a german guy learning MySQL.
I have the following problem.
3 tables joined with an inner join.

Table one contains some userdata (id, name,fullname, country_id,position_id), table two contains the name of each position (id, positionname) and table number three contains the countrydata (id, country).

Now I am trying to build a query which delivers me the number of users for each country grouped by the position.

E.G.:
======= Germany====England====USA====Overall(Sum)
=
Manager ===== 5 ======= 7 ======= 9 ====== 21
Assistant ==== 12 ======= 11 ===== 12 ======= 35
=
....

This is the query i have built up, wich seems to be incorrect.


SELECT position.name,
(Select Count(country_id) from users where country_id=1) as "germany",
(Select Count(country_id) from users where country_id=2) as "england",
(Select Count(country_id) from users where country_id=3) as "usa",
count(country_id) as "Overall"
FROM users
INNER JOIN position ON position.id = user.position_id
INNER JOIN country ON country.id = user.country_id
WHERE
country_id = 1 or
country_id = 2 or
country_id = 3
GROUP BY position.name

Can you tell me how I have to change it to get the wished result ?

Thank You
Greetings
Heiko