title: SQL query to group multiple groups
body: <p>I'm parsing some IIS server logs (with LogParser) and I'm having a little trouble creating a query that will give me the aggregate count of each type of browser (user agent).</p>
  
  <p>Here is my query so far:</p>
  
  <pre><code>SELECT COUNT(*) as totalHits, CS(User-Agent) as browser
  FROM E:\Logs\ex111101.log
  WHERE (CS(User-Agent) LIKE '%ipad%' 
         OR CS(User-Agent) LIKE '%iphone%' 
         OR CS(User-Agent) LIKE '%blackberry%'
         OR CS(User-Agent) LIKE '%windows cs%'
         OR CS(User-Agent) LIKE '%android%')
  AND cs-uri-stem LIKE '%.asp%'
  GROUP BY browser
  ORDER BY totalHits DESC
  </code></pre>
  
  <p>This givse me a list of user-agents like I want, and gives me a hit count for each group of browser:</p>
  
  <pre><code>Total Hits | Browser
  467        | AndroidA 
  45         | AndroidB 
  23         | BlackberryC
  233        | BlackberryD
  </code></pre>
  
  <p>What I want is THIS:</p>
  
  <pre><code>Total Hits | Browser 
  512        | Android 
  256        | Blackberry
  </code></pre>
  
  <p>Where All Android entries are counted and totaled under a generic Android row.  In this case Total Hits for Android would be 467 + 45, Blackberry would be 233 = 33, etc.</p>
  
  <p>From there I would want to provide a Percent column for each browser so I can represent the total hits as a percentage.</p>
  
  <p>Any help is appreciated. Thanks!</p>
  
  <p>*<strong>UPDATE</strong></p>
  
  <p>Followed the suggestion below but had to make a few tweaks in order for it to be executed correctly by LogParser.  For some reason Log Parser didn't like the LIKE keyword in the CASE statement.  The following worked fine though:</p>
  
  <pre><code>select
  case strcnt(TO_LOWERCASE(cs(user-agent)),'android') WHEN 1 THEN 'Android' else
  case strcnt(TO_LOWERCASE(cs(user-agent)),'ipad') WHEN 1 THEN 'iPad' else
  case strcnt(TO_LOWERCASE(cs(user-agent)),'blackberry') WHEN 1 THEN 'Blackberry' else
  case strcnt(TO_LOWERCASE(cs(user-agent)),'windows ce') WHEN 1 THEN 'Windows' else
  case strcnt(TO_LOWERCASE(cs(user-agent)),'iphone') WHEN 1 THEN 'iPhone' 
  ELSE 'Non-Mobile' End End End End End as Browser,
  count(*) as TotalHits
  from MYLOG
  group by Browser
  order by TotalHits desc
  </code></pre>