COUNT działa trochę inaczej, niż moglibyśmy oczekiwać, dlatego i z jednym złączeniem dane mogą być nieprawidłowe. Chyba bez podzapytań się nie obejdzie:
SELECT
[USER].name AS [Imię],
[USER].surname AS [Nazwisko],
[PISMA].number_per_user AS [Ilość pism],
[KOPERTY].number_per_user AS [Ilość kopert]
FROM
[TABELKA_USEROW] AS [USER]
LEFT JOIN (SELECT user_id, COUNT(user_id) AS number_per_user FROM [TABELKA_1] GROUP BY user_id) AS [PISMA] ON [PISMA].user_id = [USER].user_id
LEFT JOIN (SELECT user_id, COUNT(user_id) AS number_per_user FROM [TABELKA_2] GROUP BY user_id) AS [KOPERTY] ON [KOPERTY].user_id = [USER].user_id
WHERE
[USER].usergroup_id = 7
GROUP BY
[USER].name,[USER].surname,[PISMA].user_id,[KOPERTY].user_id
ORDER BY [Nazwisko]
lub
SELECT
[USER].name AS [Imię],
[USER].surname AS [Nazwisko],
(SELECT COUNT(*) FROM [TABELKA_1] AS [PISMA] WHERE [PISMA].user_id = [USER].user_id) AS [Ilość pism],
(SELECT COUNT(*) FROM [TABELKA_2] AS [KOPERTY] WHERE [KOPERTY].user_id = [USER].user_id) AS [Ilość kopert]
FROM
[TABELKA_USEROW] AS [USER]
WHERE
[USER].usergroup_id = 7
GROUP BY
[USER].name,[USER].surname,[PISMA].user_id,[KOPERTY].user_id
ORDER BY [Nazwisko]
Nie wiem, które będzie szybsze, czy oba są optymalne i czy na 100% działają, bo pisałem z głowy.