# 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

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.

copy | embed

• db, 
• mysq, 
• sum

SELECT 
      MONTHNAME(createDt) AS month,
      YEAR(createDt) AS year,
      COUNT(*) AS total
FROM tracking
GROUP BY MONTH(createDt), YEAR(createDt)
ORDER BY id

sql

1

Count Monthly/Yearly Totals in MySQL

SELECT 
      MONTHNAME(createDt) AS month,
      YEAR(createDt) AS year,
      COUNT(*) AS total
FROM tracking
GROUP BY MONTH(createDt), YEAR(createDt)
ORDER BY id

copy | embed

• count, 
• database, 
• db, 
• mysql, 
• select

// use found_rows() as a sql function
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

sql

1

Use found_rows() in MySQL

// use found_rows() as a sql function
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

copy | embed

• database, 
• db, 
• mysql, 
• select