title: Get a substring from a column and perform a groupBy and count
body: <p>I have a table that stores data about a large number of files, such as their language, unique ID, file path etc. I want to be able to get the sub-string from the unique ID which gives me the asset type, this is always the first 2 letters of the ID. I then want to group these asset types by language and have a count for how many of each type every language has. So at the end I would ideally like a table that has a language column and then a column for each substring (asset type).</p>
  
  <p>I have tried to create a large switch statement but this isn't very reliable and I was told maybe linq would be better. I don't have much experience with linq or sql and I have a couple of sql queries I've tried that gets me one part of the desired results, but I was hoping maybe someone who has more experience might know how to group these functions into one statement.</p>
  
  <pre><code>SELECT 
    LCID,
    SUBSTRING(AssetID,1,2)  
  FROM [table]
  </code></pre>
  
  <p>this gets me the correct substrings, but I have multiple rows for each language. Is there any way to group the same languages into one column and then count how many of each type there are? Thanks</p>
  
  <p><img src="https://i.stack.imgur.com/zuthJ.png" alt="sample data from current query">
  <img src="https://i.stack.imgur.com/U5MYN.png" alt="desired results"></p>