CODEWITHSUNDEEP
sqlsql-servert-sql
refaelo1979
refaelo1979

Reputation: 59

Column as a subquery

Need to have a 3rd column which will sum all points per 1st column (word) but that it will sum the points from all words that are like the '%word%' of each line, for example the word 'chef' will have the points of 'chef' and 'kitchen chef' and etc.

SELECT word, SUM(points) as points  
  FROM table
group by word

Upvotes: 1

Views: 116

Answers (3)

Nagashree Hs
Nagashree Hs

Reputation: 853

Correlated subquery within a select can reduce performance. I would recommend using join or cross apply 

select distinct w.word, C.SumOfPoints as sumofpoints
from words w
CROSS APPLY (SELECT SUM(points) AS SumOfPoints from words w2 WHERE w2.word like '%' + 
w.word + '%') C

Upvotes: 0

Ross Bush
Ross Bush

Reputation: 15185

You can test this. Joining on a LIKE is not very performant however.

;WITH Words AS(
    SELECT word ,sum(points) as points, 
    FROM table group by word
)

SELECT
    W.word,
    W.points,
    LikeWordPoints = W.points + ISNULL(SUM(LikeWord.points),0)
FROM
    Words W
    LEFT OUTER JOIN Words LikeWord ON LikeWord.word LIKE '%'+W.word+'%' AND LikeWord.word <> W.word
GROUP BY
    W.word,
    W.points

Upvotes: 1

sgeddes
sgeddes

Reputation: 62861

If I'm understanding correctly, I'd probably use a correlated subquery for this:

select distinct w.word, 
      (select sum(points) from words w2 where w2.word like '%' + w.word + '%') as points
from words w

Upvotes: 1

Related Questions