Total weight....

1. Maurice PH Hendriks Guest

## Total weight....

I have three tables:<BR><BR>WEIGHTS REGIONS REGIONIDs<BR>------- -------------------------- ---------<BR>ID WEIGHT ID REG1 REG2 REG3 REG4 ID REG<BR>1 25 1 5 5 56 21 5 RegionA<BR>2 80 2 7 5 34 75 34 RegionB<BR>.. ...... ........<BR><BR>What I am looking for is a sum of REG1*WEIGHT1 + REG2*WEIGHT1 etc...for a specific region and then a total sum of those individual sums saying RegionA = ......<BR><BR>In this case:<BR>RegionA would be 25 + 25 + 80 = 130<BR>RegionB would be 80

2. Senior Member
Join Date
Dec 1969
Posts
7,686

## RE: Total weight....

I don&#039;t see how the WEIGHT table is related. You say REG1*WEIGHT1 but I dont&#039; see that in your results. <BR><BR>(might be due to the stupid formatting of this board)<BR><BR>It&#039;s always a good idea to give an example of your db model in the form of CREATE Table and INSERT statements, that way we can easily test.<BR><BR>

3. Maurice PH Hendriks Guest

## Sorry Dutch..

I should have said the number of occurences for a specific regionID (ie the number of occurence for region 5) times the weight of that id where weight.id = regions.id and all of that added up for all the ids in regions. Does that make sense?

4. Senior Member
Join Date
Dec 1969
Posts
7,686

## RE: Sorry Dutch..

It&#039;s not a nice solution, but the following works:<BR><BR><BR>SELECT Reg, ISNULL((SELECT SUM(ISNULL(Weight,0)) FROM Regions R INNER JOIN Weights W ON R.Id = W.Id WHERE Reg1 = rg.Id),0) +<BR> ISNULL((SELECT SUM(ISNULL(Weight,0)) FROM Regions R INNER JOIN Weights W ON R.Id = W.Id WHERE Reg2 = rg.Id),0) + <BR> ISNULL((SELECT SUM(ISNULL(Weight,0)) FROM Regions R INNER JOIN Weights W ON R.Id = W.Id WHERE Reg3 = rg.Id),0) +<BR> ISNULL((SELECT SUM(ISNULL(Weight,0)) FROM Regions R INNER JOIN Weights W ON R.Id = W.Id WHERE Reg4 = rg.Id),0) Total<BR>FROM RegionIds rg<BR><BR>I can&#039;t get off the feeling the design isn&#039;t really good, is it possible to change?

5. Maurice PH Hendriks Guest

## Yes that works, but...

I have 100 variables in that table (and 400 in another!), resulting in 100 to 400 lines of code! The data is from surveys and cannot be normalised (I know, I find it frustrating)...<BR><BR>so I guess I have to work out a more efficient way...

6. Senior Member
Join Date
Dec 1969
Posts
7,686

## RE: Yes that works, but...

Ugh, are you sure it can&#039;t be normalised?<BR><BR>You should pivot the Regions table.<BR>

7. Maurice PH Hendriks Guest

## HELP!

I&#039;m lost... I don&#039;t know where to start now? I&#039;m not familar with Pivot tables. Is that the best way to go?<BR><BR>What I was thinking of was this:<BR><BR>---------------------------------<BR>@value will be passed into the SP<BR><BR>1. select the number of occurences for one record where the value = @value<BR><BR>2. multiply that number by the weight for that record (each record has an individual weight, pulled from the measure table based on the unique ID)<BR><BR>3. Do this for the the next record (and the next etc...ie for all records)<BR><BR>4. add up the multipliers from step 2. and report that as Total<BR><BR>Should I do this using a Pivot table?

8. Senior Member
Join Date
Dec 1969
Posts
7,686

## RE: HELP!

Well the problem with your approach is that it&#039;s procedural based, instead of setbased. You would need a cursor or for/next loop that&#039;s a really bad solution.<BR><BR>The big question is, can you change the db design?

9. Maurice PH Hendriks Guest

## All done..

..I worked it al out. Thank you for your time Dutch. Much appreciated.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•