title: MySQL Query for domains and post numbers
body: <p>I have a MySQL DB table <code>users</code> where two of its columns have <code>email</code> addresses and <code>post</code> counts for each member from a forum. </p>
  
  <p>I can pull the total of users registered with specific email domains with following:</p>
  
  <pre><code>SELECT substring_index(email, '@', -1), COUNT(*) AS emailcounts
  FROM `users`
  GROUP BY substring_index(email, '@', -1)
  ORDER BY emailcounts DESC;
  </code></pre>
  
  <p>and the results from this query give me the following:</p>
  
  <pre><code>gmail.com 147
  yahoo.com 112
  hotmail.com 101
  aol.com 98
  live.com 84
  </code></pre>
  
  <p>Where e.g. 147 is the number of users who have registered through gmail.com</p>
  
  <p>My question is:</p>
  
  <p>What would be the correct MySQL query, so when you run it against the <code>users</code> table by selecting <code>email</code> and <code>post</code> columns, the results would also include the total sum of posts from users with email domains like below:</p>
  
  <pre><code>gmail.com 147  1559
  yahoo.com 112  972
  hotmail.com 101  833
  aol.com 98  612
  live.com 84  420
  </code></pre>
  
  <p>E.g. From 147 users who registered from gmail.com we have a TOTAL 1559 posts</p>
  
  <p>I tried now with several options <a href="https://stackoverflow.com/questions/2440429/mysql-query-to-count-unique-domains-from-email-address-field">discussed here in SO</a> , but I cannot make it work as above</p>
  
  <p>I hope this edit helps in explaining the situation.</p>
  
  <p>Thanks</p>