Help on SUM

Hi,

I have two tables.

table1

ID | EmpName | Salary | LocationCode
1 | aaaaaa | 1000 | 001
2 | aaaaaa | 2000 | 002
3 | aaaaaa | 3000 | 003
4 | aaaaaa | 4000 | 004
5 | aaaaaa | 5000 | 005

table2

ID | Loc_Code | Region
1 | 001,002 | 100
2 | 003,004,005 | 200

Locations inturn grouped in to Regions as shown in table2

Now i need to find sum of salaries in each region.

I have tried in this way but failed.
SELECT sum(salary) FROM table1 WHERE LocationCode in (SELECT Loc_Code FROM table2 where Region=100)

Can you please tell solution how to find sum of salares based on region.

Does the "table2" table actually contain the string '001,002' and '003,004,005' for the "Loc_Code" column? If so, can this be changed?

|||Yes, table2 contains exactly same data. It cant be changed as application is already in production.
|||

This is not a good design. While join your query performance will be very poor. Change the comma separated values into Rows.

Anyhow here the query for your current design,

Code Snippet

Create Table #table1 (

[ID] int ,

[EmpName] Varchar(100) ,

[Salary] int ,

[LocationCode] Varchar(100)

);

Insert Into #table1 Values('1','aaaaaa','1000','001');

Insert Into #table1 Values('2','aaaaaa','2000','002');

Insert Into #table1 Values('3','aaaaaa','3000','003');

Insert Into #table1 Values('4','aaaaaa','4000','004');

Insert Into #table1 Values('5','aaaaaa','5000','005');

Create Table #table2 (

[ID] int ,

[Loc_Code] Varchar(100) ,

[Region] Varchar(100)

);

Insert Into #table2 Values('1','001,002','100');

Insert Into #table2 Values('2','003,004,005','200');

Select Region,Sum([Salary]) from #table1 A

Join #table2 B on ',' + [Loc_Code] + ',' Like '%,' + A.[LocationCode] + ',%'

Group By

Region