title: How can I sum a column based on like?
body: <p>I have a SQL table <code>AddressCount</code> that has 3 fields: <code>address</code>, <code>state</code>, and <code>numEntities</code>. What I want to do is get the sum of numEntities for each address + state combination based on a like. This is somewhat hard to describe, but I think the following correlated query represents correctly what I'm trying to accomplish:</p>
  
  <pre><code>SELECT Address, State, 
      (SELECT SUM(NumEntities)
      FROM AddressCount innerQry 
      WHERE innerQry.address LIKE '%' + outerQry.address + '%' 
          AND innerQry.state = outerQry.state) As NumEntities
  FROM AddressCount outerQry
  </code></pre>
  
  <p>Basically, I want to end up with a table that has an address, a state, and the sum of numEntities for all addresses like that address surrounded by wildcards. So, if the address is "25 Courtland Rd" and the state is NJ, I want to get the sum of numEntities for all rows that have a state of NJ and an address like '%25 Courtland Rd%'. I'd love to be able to accomplish this with a GROUP BY LIKE, but I don't think this is possible with SQL. The basic concept is to accomplish something like this:</p>
  
  <pre><code>SELECT address, state, sum(numEntities)
  FROM AddressCount
  GROUP BY (LIKE '%' + address + '%')
  </code></pre>
  
  <p>The correlated query sql statement I posted above seems accurate, but I'd like to accomplish the same thing, if possible, by an inner join of the table on itself. I recently read that it's possible to join tables on a LIKE expression, so I want to accomplish something like the following:</p>
  
  <pre><code>SELECT t1.Address, t1.State, SUM(t2.NumEntities)
  FROM AddressCount t1
  INNER JOIN AddressCount t2
      ON t1.state = t2.state
      AND t2.address LIKE '%' + t1.address + '%'
  </code></pre>
  
  <p>This doesn't seem to work, though... any idea how I can accomplish this?</p>