How can I sum a column based on like?

I have a SQL table AddressCount that has 3 fields: address, state, and numEntities. 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:

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

Basically, I want to end up with a table that has an addre开发者_运维知识库ss, 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:

SELECT address, state, sum(numEntities)
FROM AddressCount
GROUP BY (LIKE '%' + address + '%')

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:

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 + '%'

This doesn't seem to work, though... any idea how I can accomplish this?

If I understand you correctly, adding GROUP BY t1.state, t1.address should work.

You just need to add

GROUP BY t1.Address, t1.State

...at the end of your second query. Just did a quick test with PostgreSQL and combining LIKE and GROUP BY works as expected, so i see no reason it schouldn't in your case.

maybe:
SELECT x.state, x.address, SUM(x.NumEntities)
FROM (SELECT * FROM AddressCount WHERE address LIKE %) x
GROUP BY x.state

Could you try this one?

SELECT a.Address,t.State,t.numEntities
FROM
  dbo.AddressCount a,
        (SELECT     State,SUM([Count]) as numEntities
        FROM dbo.AddressCount
        WHERE Address LIKE '%+address+%' 
        GROUP BY State ) t
WHERE a.State = t.State