title: SQL error Unknown column in field list using multiple subquery
body: <p>I'm trying to print the text based on the column. My idea was union all the count on each occupation and use a case to display it. But I'm not sure what is wrong with this code. Getting a error as sCount is not in field list. Any help is appreciated.</p>
  <pre><code>SELECT  CASE
  
          WHEN Occupation = &quot;doctor&quot; THEN
          CONCAT(&quot;There are a total of &quot;, dCount, &quot; &quot; , Occupation, &quot;s.&quot;)
          
          WHEN Occupation = &quot;singer&quot; THEN
          CONCAT(&quot;There are a total of &quot;, sCount, &quot; &quot; , Occupation, &quot;s.&quot;)
          
          END
  FROM (
      SELECT * FROM (
          SELECT COUNT(Occupation) AS dCount, Lower(Occupation) AS Occupation FROM OCCUPATIONS WHERE Occupation = 'Doctor'
      UNION
          SELECT COUNT(Occupation) AS sCount, Lower(Occupation) AS Occupation FROM OCCUPATIONS WHERE Occupation = 'Singer'
      ) AS s
  ) AS m;
  
  // this didnt work either
  SELECT  CASE
  
          WHEN Occupation = &quot;doctor&quot; THEN
          CONCAT(&quot;There are a total of &quot;, dCount, &quot; &quot; , Occupation, &quot;s.&quot;)
          
          WHEN Occupation = &quot;singer&quot; THEN
          CONCAT(&quot;There are a total of &quot;, sCount, &quot; &quot; , Occupation, &quot;s.&quot;)
          
          END
  FROM (
      SELECT COUNT(Occupation) AS dCount, Lower(Occupation) AS Occupation FROM OCCUPATIONS WHERE Occupation = 'Doctor'
      UNION
      SELECT COUNT(Occupation) AS sCount, Lower(Occupation) AS Occupation FROM OCCUPATIONS WHERE Occupation = 'Singer'
  ) AS m;
  </code></pre>