title: SQL Query join with a count and avg from another table
body: <p>Actually the math doesn't seem to be adding up. </p>
  
  <p>Here is the query I'm using and the 2 table's i'm trying to access (with all real data).</p>
  
  <p>This query in SQL is pulling <code>Steve Smith</code> to have 27 sum and 27 count of <code>amount</code>. It should be 17. In the same query it is showing <code>John Smith</code> to have 4 sum and 2 count of <code>amount</code>.</p>
  
  <pre><code>SELECT
  user.*,
  IFNULL(SUM(collection.amount), 0) AS usertotal,
  COUNT(collection.amount) AS userunique
  FROM user
  LEFT JOIN collection
  ON user.id = collection.userid
  GROUP BY collection.amount`
  </code></pre>
  
  <p>Table 1 (named <code>user</code>):</p>
  
  <pre><code>id | firstname | lastname | username | email
  1  | Steve     | Smith    | SteveS   | Steve@Smith.com
  2  | John      | Smith    | JohnSmith| John@Smith.com
  </code></pre>
  
  <p>Table 2 (named <code>collection</code>):</p>
  
  <pre><code>id|userid|carid |amount
  1   1   74  1
  10  2   130 1
  11  2   48  1
  12  2   414 1
  13  2   415 1
  14  2   66  1
  15  2   404 1
  16  2   57  2
  17  2   331 1
  18  2   264 1
  19  2   325 1
  20  2   51  2
  21  2   185 1
  24  1   168 1
  25  1   11  1
  26  1   315 1
  27  1   51  1
  28  1   210 1
  29  1   433 1
  30  1   434 1
  31  1   460 1
  32  1   75  1
  33  1   238 1
  34  1   226 1
  35  1   396 1
  36  1   174 1
  37  1   12  1
  38  1   328 1
  39  1   4   1
  
  
  
  id| UN | Amount
  1 | 4  | 457
  2 | 4  | 28
  3 | 2  | 234
  4 | 1  | 235
  5 | 2  | 1
  </code></pre>
  
  <p>I need a query to get the entire list of names of users, and inner join (with sum and count on the amount column) between user.id and collection.UN</p>
  
  <p>I don't know the SQL query, can't figure it out for the life of me. Help?</p>
  
  <p>Thanks,</p>