Public snipts » robertbanh's snipts » Sum of 2 columns with alias

• # remove the group by if you want 1 total row returned.
  #
  select *, sum(s1.score+s2.score) total
  from search_score s1
  inner join search_word w1 on s1.word_id = w1.id
  inner join search_score s2 on s1.sp_id = s2.sp_id and s1.id != s2.id
  inner join search_word w2 on s2.word_id = w2.id
  where 
  w1.word = 'zinc' and w2.word = 'soft'
  group by s1.id
  order by total desc
  limit 50
  
  # updated note: if this will span more then 2 and goes nth level, then it's
  # faster to run each word search separately. unset vars as needed.

  mysql

  1

  # remove the group by if you want 1 total row returned.
  #
  select *, sum(s1.score+s2.score) total
  from search_score s1
  inner join search_word w1 on s1.word_id = w1.id
  inner join search_score s2 on s1.sp_id = s2.sp_id and s1.id != s2.id
  inner join search_word w2 on s2.word_id = w2.id
  where 
  w1.word = 'zinc' and w2.word = 'soft'
  group by s1.id
  order by total desc
  limit 50
  
  # updated note: if this will span more then 2 and goes nth level, then it's
  # faster to run each word search separately. unset vars as needed.
  

  copy | embed

0 Comments