CODEWITHSUNDEEP
sqlsql-servert-sql
James
James

Reputation: 16339

Multiplying common values between 2 tables

I have two tables, a word frequency table and a word weight table. I need to write a t-sql script or scripts to calculate a weighted score of the word frequency table based on the words and weights given in the word weight table.

For example: Word frequency table

Word   Frequency
,,,,,,,,,,,,,,,,
Fat        3
Ugly       2
dumb       2
stupid     3

Word weight table

Word    Weight
,,,,,,,,,,,,,,
Ugly      5
stupid    7

The weighted score from these two tables would work out to be (5x2)+(7x3)=31 I then need to print the results, if over 30 "Alert! Score over 30" or if under 30 then "Normal, score under 30".

I'm fine with creating the print script once the score is calculated, but I'm not too sure how to get there.

The scripts need to be able to allow for the tables to be altered, so I'm guessing it just needs to look for common values between them and then join the columns.

I could be way off but I'm figuring a join between the two tables based on where w.word = f.word ??

I've been looking for a solution all afternoon and really haven't gotten anywhere. Any help would be appreciated!

Upvotes: 0

Views: 93

Answers (4)

xacinay
xacinay

Reputation: 909

Just to prove @wraith answer, here is the code: 

declare @WordFreq table (Word varchar(max), Frequency int );
declare @WordWeight table (Word varchar(max), Weight int );

insert into @WordFreq( Word, Frequency ) values
  ( 'Fat', 3)
, ( 'Ugly', 2)
, ( 'dumb', 2)
, ( 'stupid', 3)

insert into @WordWeight( Word, Weight ) values
  ( 'Ugly', 5)
, ( 'stupid', 7)

select sum (w.Weight * f.Frequency)
  from @WordFreq f
join @WordWeight w on f.Word = w.Word
-----------------------
OUTPUT: 31

Upvotes: 0

Adi
Adi

Reputation: 232

select  case when SUM( cast(fr as numeric )* cast (weight as numeric))  >30 then 'ABove 30'
else 'below 30' end from table1 inner join 
table2 on table1.word=table2.word

Upvotes: 0

T I
T I

Reputation: 9933

If one table contains all the words then you could use a left join as suggested but if not then a full outer join would work.

SELECT 
    COALESCE(t1.word, t2.word) AS word
    , COALESCE(t1.frequency, 1) AS frequency
    , COALESCE(t2.weight, 1) AS weight
    , COALESCE(t1.frequency, 1) * COALESCE(t2.weight, 1) AS score
    , CASE WHEN COALESCE(t1.frequency, 1) * COALESCE(t2.frequency, 1) > 30 
                THEN 'Alert! Score over 30'
                ELSE 'Normal, score under 30' AS message END
FROM word_frequency t1
FULL OUTER JOIN word_weight t2
ON t1.word = t2.word

Upvotes: 0

wraith
wraith

Reputation: 351

It should be 

select sum (w.Weight * f.Frequency) from WeightTable w
join FreqTable f  on f.Word = w.Word

Upvotes: 4

Related Questions