title: MySQL count columns on specific value
body: <p>I have the following db table, and I would like to be able to count the instance of sales of certain products per salesperson.</p>
  <pre><code>|------------|------------|------------|
  |id          |user_id     |product_id  |
  |------------|------------|------------|
  |1           |1           |2           |
  |2           |1           |4           |
  |3           |1           |2           |
  |4           |2           |1           |
  |------------|------------|------------|
  </code></pre>
  <p>I would like to able to create a result set like the following;</p>
  <pre><code>|------------|-------------|------------|------------|------------|
  |user_id     |prod_1_count |prod_2_count|prod_3_count|prod_4_count|
  |------------|-------------|------------|------------|------------|
  |1           |0            |2           |0           |1           |
  |2           |1            |0           |0           |0           |
  |------------|-------------|------------|------------|------------|
  </code></pre>
  <p>I am creating graphs with this data, and once again (as earlier today) I am unable to count the column totals. I have tried;</p>
  <pre><code>SELECT user_id, 
  (SELECT count(product_id) FROM sales WHERE product_id = 1) AS prod_1_count,
  (SELECT count(product_id) FROM sales WHERE product_id = 2) AS prod_2_count,
  (SELECT count(product_id) FROM sales WHERE product_id = 3) AS prod_3_count,
  (SELECT count(product_id) FROM sales WHERE product_id = 4) AS prod_4_count 
  FROM sales GROUP BY user_id; 
  </code></pre>
  <p>I can see why this doesn't work, because for each bracketed SELECT the user_id doesn't match the external user_id in the main SELECT statement.</p>