title: Optimize SQL with sub queries to?
body: <p>I have following two tables in the bottom of the post. The siteUsers table have a type column that can be "Member" or "Owner". I am looking for a SQL that shows how many members and owners each site has. I could do something like:</p>
  
  <pre><code>select 
      sites.Url,
      (select count(*) from siteusers where type='member' and siteusers.siteid=sites.siteid) as members,
      (select count(*) from siteusers where type='owners' and siteusers.siteid=sites.siteid) as owners
      from sites
      group by sites.url
  </code></pre>
  
  <p>However this is extreemly slow. Is there a faster and smarter way?</p>
  
  <p>Sites table:</p>
  
  <pre><code>SiteId  int Unchecked
  SPOSiteId   uniqueidentifier    Checked
  Url nvarchar(MAX)   Checked
  </code></pre>
  
  <p>SiteUsers Table:</p>
  
  <pre><code>SiteUserId  int Unchecked
  Type    nvarchar(256)   Checked
  Name    nvarchar(512)   Checked
  Email   nvarchar(512)   Checked
  Host    nvarchar(256)   Checked
  SiteId  int Checked
  </code></pre>