title: Using SQL Aggregate Functions With Multiple Joins
body: <p>I am attempting to use multiple aggregate functions across multiple tables in a single SQL query (using Postgres).</p>
  
  <p>My table is structured similar to the following:</p>
  
  <pre><code>CREATE TABLE user (user_id INT PRIMARY KEY, user_date_created TIMESTAMP NOT NULL);
  CREATE TABLE item_sold (item_sold_id INT PRIMARY KEY, sold_user_id INT NOT NULL);
  CREATE TABLE item_bought (item_bought_id INT PRIMARY KEY, bought_user_id INT NOT NULL);
  </code></pre>
  
  <p>I want to count the number of items bought and sold for each user.  The solution I thought up does not work:</p>
  
  <pre><code>SELECT user_id, COUNT(item_sold_id), COUNT(item_bought_id)
  FROM user
  LEFT JOIN item_sold ON sold_user_id=user_id
  LEFT JOIN item_bought ON bought_user_id=user_id
  WHERE user_date_created &gt; '2014-01-01'
  GROUP BY user_id;
  </code></pre>
  
  <p>That seems to perform all the combinations of (item_sold_id, item_bought_id), e.g. if there are 4 sold and 2 bought, both COUNT()s are 8.</p>
  
  <p>How can I properly query the table to obtain both counts?</p>