Group by clause with multiple columns

1

--I've got a problem with this SUM and group by clause. I've tried to simplify all the table names for clarity and removed the joins but let me know if they are pivotal in solving this.

This bit below works ok, as expected:


SELECT        M.WorkID, SUM(dbo.Pay.AMOUNT) AS Amount, dbo.Countries.towns
FROM      
myTable...
bunch of inner Joins here...


GROUP BY M.WorkID, dbo.Countries.towns
HAVING        (dbo.Countries.towns LIKE '%ville') AND (M.WorkID = '007')

WorkID - - Amount - - Towns
007 ---------- 12 --------- Smallville
007 ---------- 1004 ------ Nashville

However, I want to sum up all the *ville towns together, and so get '1014' in the result above for Amount.
I can't remove towns from the group by clause alone because its in the Select and Having clause and would error.
And if I remove towns from the entire query it looks like it adds up every column in the database table (even ones that don't have ID 007).

Anybody know what I need to do to sum up this Amount field and group certain towns together (and so it should give me the expected answer of 1014?)

TIA

2

Those conditions should be in a WHERE clause, not a HAVING clause:


SELECT        M.WorkID, SUM(dbo.Pay.AMOUNT) AS Amount
FROM      
WHERE         (dbo.Countries.towns LIKE '%ville') AND (M.WorkID = '007')
GROUP BY M.WorkID
3 Likes
3

thank you. that works. I didn't know you could put a where clause within the same select statement as a Group By.

4

Thank you for helping me out as well.

5

On a slightly different note... code like...

WHERE dbo.Countries.towns LIKE '%ville'

... makes for "non-SARGable" criteria that cannot do an index seek and so, at best, will do at least a full non-clustered index scan or a scan of the clustered index or heap, which is going to make you code slow, read intensive, and have basically no scalability.

It's the leading wildcard, in this case.