title: SQL question - why i can't solve a problem
body: <p>Why my solution of a problem on the study site is not accepted and showing &quot;wrong answer&quot;?</p>
  <p>Here is the problem - <a href="https://www.hackerrank.com/challenges/the-pads/problem/" rel="nofollow noreferrer">https://www.hackerrank.com/challenges/the-pads/problem/</a> :</p>
  <blockquote>
  <p>Generate the following two result sets:</p>
  <ol>
  <li><p>Query an alphabetically ordered list of all names in <code>OCCUPATIONS</code>, immediately followed by the first letter of each profession as a parenthetical (i.e.: enclosed in parentheses). For example: AnActorName(A), ADoctorName(D), AProfessorName(P), and ASingerName(S).</p>
  </li>
  <li><p>Query the number of occurrences of each occupation in <code>OCCUPATIONS</code>. Sort the occurrences in ascending order, and output them in the following format:</p>
  </li>
  </ol>
  <p>There are a total of [occupation_count] [occupation]s.</p>
  <p>where [occupation_count] is the number of occurrences of an occupation in <code>OCCUPATIONS</code> and [occupation] is the lowercase occupation name. If more than one Occupation has the same [occupation_count], they should be ordered alphabetically.</p>
  </blockquote>
  <p>My query is:</p>
  <pre><code>SELECT 
      CAST(NAME, CASE 
                    WHEN occupation = 'Actor' THEN '(A)' 
                    WHEN occupation = 'Doctor' THEN '(D)' 
                    WHEN occupation = 'Professor' THEN '(P)' 
                    WHEN occupation = 'Singer' THEN '(S)' 
                 END) 
  FROM   
      occupations; 
  </code></pre>
  <p>and</p>
  <pre><code>SELECT 
      CASE 
         WHEN occupation = 'Actor' 
            THEN CONCAT('There are a total of ', COUNT(occupation), ' ', LOWER(occupation), 's.') 
         WHEN occupation = 'Doctor' 
            THEN CONCAT('There are a total of ', COUNT(occupation), ' ', LOWER(occupation), 's.') 
         WHEN occupation = 'Singer' 
            THEN CONCAT('There are a total of ', COUNT(occupation), ' ', LOWER(occupation), 's.') 
         WHEN occupation = 'Professor' 
            THEN CONCAT('There are a total of ', COUNT(occupation), ' ', LOWER(occupation), 's.') 
     END 
     FROM
         occupations 
     GROUP BY 
         occupation 
     ORDER BY 
         COUNT(occupation), occupation;  
  </code></pre>
  <p>The query has same output like in the example on HackerRank. What am I doing wrong?</p>