title: How do you group by a case statement with a count function?
body: <p>I am trying to create a distribution based on counts. I have a dataset where the rows represent sales. There is a discount_code variable, which determines the discount_code used in the sale. There are 200 distinct discount codes.</p>
  <p>I want to count the distinct discount codes that fall in grouped sales counts. For example, how many discount codes had 25-100 sales associated with it? This will tell me the distribution of successful codes (if success is a discount code having many sales).</p>
  <p>I wrote this code to start:</p>
  <pre><code>SELECT discountcode,
      CASE
      WHEN COUNT(sales) &lt; 25 THEN 'A. &lt;25 sales'
      WHEN COUNT(sales) &lt; 100 AND COUNT(sales) &gt; 25 THEN 'B. &lt;100 sales'
      WHEN COUNT(sales) &lt; 200 AND COUNT(sales) &gt; 100 THEN 'C. &lt;200 sales'
      WHEN COUNT(sales) &lt; 500 AND COUNT(sales) &gt; 200 THEN 'D. &lt;500 sales'
      WHEN COUNT(sales) &lt; 1000 AND COUNT(sales) &gt; 500 THEN 'E. &lt;1,000 sales'
      WHEN COUNT(sales) &lt; 5000 AND COUNT(sales) &gt; 1000 THEN 'F. &lt;5,000 sales'
      ELSE 'G. &gt;5,000 sales'
      END AS discount_code_dist
  FROM table
  GROUP BY discountcode;
  </code></pre>
  <p>I can't group by an aggregate function, which is where I get stuck. I want to group by discount_code_dist. I think I need to count and group by  within my case, but have not figured out how to do it.</p>
  <p>I am trying not to use a subquery. Therefore, I prefer to know how to do it without one. However, I am curious to know how a subquery would do it (especially if that is the only way).</p>