title: SQL - Obtaining a criterion-limited and total count - is a subquery needed?
body: <p>I'm trying to obtain the number of orders made by each of our customers that has ordered within the last 31 days, as well as the total number that they've ever made. My desired output would look like:</p>
  
  <pre><code>count    total    name           customer ID
  --------------------------------------------
  3        10       Sarah Smith    22
  2        8        John Jenkins   15
  1        5        Delia Denton   44
  </code></pre>
  
  <p>So far I have the following, which works perfectly for the first requirement:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT COUNT(o.id), CONCAT(c.first_name, ' ', c.last_name) AS name, c.id AS "customer ID"
  FROM orders o
  JOIN customers c ON c.id = o.customer_id
  WHERE order_date &gt; (CURRENT_DATE - 31)
  GROUP BY name, "customer ID"
  ORDER BY count DESC
  </code></pre>
  
  <p>Which produces: </p>
  
  <pre><code>count    name           customer ID
  -----------------------------------
  3        Sarah Smith    22
  2        John Jenkins   15
  1        Delia Denton   44
  </code></pre>
  
  <p>However, my limited knowledge of subqueries doesn't extend to how I can integrate one to retrieve the total count of orders for each customer that features in this result set. (Essentially the same query but without the date limitation.) I'm not sure whether (or how) I should be using this as a filter somehow on the total set of all orders, or whether the correct thing is to take the IDs returned here and plug them into another query. What should I do? I'm on PostgreSQL 9.4.</p>