I have 3 tables:

1. Profiles:

2. Questions:

3. Votes

I want to construct the following table:

I tried this:

 SELECT profiles.id,
    profiles.created_at,
    profiles.full_name,
    profiles.avatar_url,
    count(questions.owner_id) AS questions_published,
    count(votes.user_id) AS votes_cast
  FROM public.profiles
  LEFT JOIN public.questions
  ON profiles.id = questions.owner_id
  LEFT JOIN votes
  ON profiles.id = votes.user_id
  GROUP BY profiles.id;

The result looks like this:

but should looks like this:

CodePudding user response：

You are cross joining a users questions and votes. If a user asked two questions and cast two votes, you create four result rows of all combinations (2 x 2 = 4). If you count then, you will find four rows with questions and four rows with votes.

Instead, aggregate before joining, i.e. join the user's question count and vote count to their profile.

SELECT
  p.id,
  p.created_at,
  p.full_name,
  p.avatar_url,
  COASLESCE(q.cnt, 0) AS questions_published,
  COASLESCE(v.cnt, 0) AS votes_cast
FROM public.profiles p
LEFT JOIN 
(
  SELECT owner_id, COUNT(*) AS cnt
  FROM public.questions
  GROUP BY owner_id
) q ON q.owner_id = p.id
LEFT JOIN 
(
  SELECT user_id, COUNT(*) AS cnt
  FROM votes
  GROUP BY user_id
) v ON v.user_id = p.id
ORDER BY p.id;