title: SQL Query and case statement to show multiple entries by joining two tables
body: <p>Here is an example of two tables and I would like to create a view as end result. There are two tables as input to the view, one that has countries with location id and the other has location names, I want to join them and then do a case to replace multiple counts with single entry.
  Any help is appreciated.</p>
  
  <p><a href="https://i.stack.imgur.com/li1OI.png" rel="nofollow noreferrer">Here is the screenshot of how tables look like, Table Entity and Table Location as input tables to the view</a></p>
  
  <p>I first thought of getting counts of multiple location Id from Entity Table and then create a select Case statement but no avail, here is the draft code.</p>
  
  <pre><code>select
      Country, count(locationid),
      case LocationName
          when Cnt &gt; 1 then 'Mulitple offices'
          when Cnt = 0 then 'Unknown Location'
          else LocationName
      end
  from 
      Entity 
  group by 
      Country
  </code></pre>
  
  <p>UPDATE:
  I just made a <a href="http://www.sqlfiddle.com/#!6/da9a0/2" rel="nofollow noreferrer">SQLFiddle</a> (pretty handy) and realized both solutions from @Gordon and @P.Eh but with some issues. See specific in comments below</p>