title: MySQL query - counting values in query
body: <p>here's my example table (assuming i'm having 3 categories only):</p>
  
  <pre><code>id user    item    category
  ----------------------------
  1  myuser  item1   1
  2  myuser  item2   2
  3  myuser  item3   2
  4  myuser  item4   2
  5  myuser  item5   2
  6  myuser  item6   3
  </code></pre>
  
  <p>i'm trying to do a query which sums the categories in order to get this result:</p>
  
  <pre><code>user    cat_1   cat_2   cat_3
  --------------------------------
  myuser  1       4       1
  </code></pre>
  
  <p>what's the best method?
  thanks</p>
  
  <p><strong>EDIT:</strong> extended approach (sorry my mistake)</p>
  
  <p>my table as follows:</p>
  
  <pre><code>id user    item    category  total
  ------------------------------------
  1  myuser  item1   1         2
  2  myuser  item2   2         6
  3  myuser  item3   3         4
  </code></pre>
  
  <p>how can i query it to get this result:</p>
  
  <pre><code>user    cat_1   cat_2   cat_3
  --------------------------------
  myuser  2       6       4
  </code></pre>