Two SQL LEFT JOINS produce incorrect result

Joins are processed left to right (unless parentheses dictate otherwise). If you LEFT JOIN (or just JOIN, similar effect) three groceries to one user you get 3 rows (1 x 3). If you then join 4 fishmarkets for the same user, you get 12 (3 x 4) rows, multiplying the previous count in the result, not adding to it, like you may have hoped for.
Thereby multiplying the visits for groceries and fishmarkets alike.

You can make it work like this:

SELECT u.id     , u.account_balance     , g.grocery_visits     , f.fishmarket_visitsFROM   users uLEFT   JOIN (   SELECT user_id, count(*) AS grocery_visits   FROM   grocery   GROUP  BY user_id   ) g ON g.user_id = u.idLEFT   JOIN (   SELECT user_id, count(*) AS fishmarket_visits   FROM   fishmarket   GROUP  BY user_id   ) f ON f.user_id = u.idORDER  BY u.id;

To get aggregated values for one or few users, correlated subqueries like @Vince provided are just fine. For a whole table or major parts of it, it is (much) more efficient to aggregate the n-tables and join to the result once. This way, we also do not need another GROUP BY in the outer query.

grocery_visits and fishmarket_visits are NULL for users without any related entries in the respective tables. If you need 0 instead (or any arbitrary number), use COALESCE in the outer SELECT:

SELECT u.id     , u.account_balance     , COALESCE(g.grocery_visits   , 0) AS grocery_visits     , COALESCE(f.fishmarket_visits, 0) AS fishmarket_visitsFROM   ...

For your original query, if you take away the group by to look at the pre-grouped result, you'll see why the counts your were receiving were created.

Perhaps the following query utilizing subqueries would achieve your intended result:

SELECT t1."id" AS "User ID", t1.account_balance AS "Account Balance", (SELECT count(*) FROM grocery     t2 ON (t2.user_id=t1."id")) AS "# of grocery visits", (SELECT count(*) FROM fishmarket  t3 ON (t3.user_id=t1."id")) AS "# of fishmarket visits"FROM users t1ORDER BY t1.id

It's because when the user table joins to the grocery table, there are 3 records matched. Then each of those three records matches with the 4 records in fishmarket, producing 12 records. You need subqueries to get what you are looking for.