title: Ordering MySQL 8 results by count existence in a crosswalk table
body: <p>I have the following MySQL 8 tables:</p>
  <pre><code>[submissions]
  ===
  id
  submission_type
  name
  
  [reject_reasons]
  ===
  id
  name
  
  [submission_reject_reasons] -- crosswalk joining the first 2 tables
  ===
  id
  submission_id
  reject_reason_id
  </code></pre>
  <p>In my application, users can submit submissions, and other users can request changes to those submissions. When they request these rejections, 1+ entries get saved to the <code>submission_reject_reasons</code> table (which stores the ID of the submission for which rejections are requested, as well as the ID of the reason for why the rejection is being made). So a typical entry in the table might look like:</p>
  <pre><code>id      submission_id     reject_reason_id
  ==============================================
  45      384               294
  </code></pre>
  <p>Where <code>submission_id = 384</code> is the &quot;Fizz Buzz&quot; submission and <code>reject_reason_id = 294</code> is the &quot;<em>Missing Required Field</em>&quot; reason.</p>
  <p>I currently have a query that fetches all the <code>reject_reasons</code> out of the DB:</p>
  <pre><code>SELECT * FROM reject_reasons
  </code></pre>
  <p>I now want to modify this query to sort the results based on their usage frequency. Meaning the query might currently return:</p>
  <pre><code>294 | Missing Required Field
  14 | Malformed Entry
  1885 | Makes No Sense
  </code></pre>
  <p>etc. But lets say there are 5 entries in the <code>submission_reject_reasons</code> table where 294 (Missing Required Field) is the <code>reject_reason_id</code>, and say there are 15 enries where 1885 (Makes No Sense) is present, and 120 entries where 14 (Malformed Entry) are present. I need a query that returns all <code>reject_reasons</code> sorted by their count in the <code>submission_reject_reasons</code> (SRR) table, descending, so that the most frequently used appear earlier in the sort. Hence the result set would be:</p>
  <pre><code>14 | Malformed Entry --&gt; because there are 120 instances of this in the SRR table
  1885 | Makes No Sense --&gt; because there are 15 instances in the SRR
  294 | Missing Required Field --&gt; because there are only 5 instances in the SRR
  </code></pre>
  <p>Furthermore, I need a ranking from most-used to least-used. If a reason doesn't exist in the SRR table it should have a default &quot;count&quot; of zero (0) but should still come back in the query. If 2+ reason counts are tied, then I don't care how they are sorted. Any ideas here? I need the final result set to only contain the <code>rr.id</code> and <code>rr.name</code> field/values.</p>
  <p>My best attempt is not getting me anywhere:</p>
  <pre><code>SELECT rr.id, rr.name
  FROM reject_reasons AS rr
  LEFT JOIN submission_reject_reasons AS srr on rr.id = srr.reject_reason_id
  GROUP BY rr.id
  ORDER BY COUNT(*) DESC
  </code></pre>
  <p>Can anyone help me over the finish line here? Can anyone spot where I'm goin awry? Thanks in advance!</p>