MySQL – Join Results and Sum of Likes in Different Table

MySQL

I have two databases, articles and article-likes.
I need the query to select everything from the article and add a row which is the sum of likes (we have -1 for dislike and +1 for like) from the table article-likes by searching for their ID.

Articles table:

Article-Likes table:

For this instance, the article with ID Lorem-Ipsum should have 2 likes. On the other hand, Sit-Amet should have 1 like (1 + -1).

Best Answer

You can use a subquery to sum all likes of each article.

SELECT author,
       name,
       text,
       id,
       (SELECT SUM(value)
        FROM Articles-Likes
        WHERE articles_id = Articles.ID) as Likes  
from   Articles

Related Solutions

Mysql – How to use SUBSTRING using REGEXP in MySQL

This would need to use the LOCATE and SUBSTRING syntax to get the information out of the string. The basic locate syntax you would need is explained here.

LOCATE (search str, str, [position])

search str = A string which will be searched for.

str =A string which is going to be searched.

position (optional)= Position from where (within the second argument) the searching will start .

While the substring function you need is explained here

SUBSTRING(str, pos, len)

str= A string.

pos= Starting position.

len= Length in characters.

The easier way of viewing this is to think of substring as the following SUBSTRING(str FROM pos FOR len)

The sytax I used to get the second word is below, I took advantage of the spaces that are constantly around the second word you are trying to extract.

declare @String varchar(50) ='Lorem D9801 ipsum dolor sit amet'

SUBSTRING
(
@String,
LOCATE(' ', @String),
LOCATE(' ', @String, (LOCATE(' ', @String) + 1)) - LOCATE(' ', @String)
)

MySQL count multiple columns and sum the total occurrence

This is a query with an idea like in MySQL count multiple columns and sum the total occurrence

SELECT 
drawno, 
SUM(CASE WHEN FIRST = 0 THEN 1 ELSE 0 END) zero,
SUM(CASE WHEN FIRST = 1 THEN 1 ELSE 0 END) one,
SUM(CASE WHEN FIRST = 2 THEN 1 ELSE 0 END) two,
SUM(CASE WHEN FIRST = 3 THEN 1 ELSE 0 END) three,
SUM(CASE WHEN FIRST = 4 THEN 1 ELSE 0 END) four,
SUM(CASE WHEN FIRST = 5 THEN 1 ELSE 0 END) five,
SUM(CASE WHEN FIRST = 6 THEN 1 ELSE 0 END) six,
SUM(CASE WHEN FIRST = 7 THEN 1 ELSE 0 END) seven,
SUM(CASE WHEN FIRST = 8 THEN 1 ELSE 0 END) eight,
SUM(CASE WHEN FIRST = 9 THEN 1 ELSE 0 END) nine
FROM 
(
  SELECT 
    first
    ,drawno 
  FROM table1 
  UNION ALL 
  SELECT 
     second
     ,drawno 
  FROM table1 
  UNION ALL 
  SELECT 
    third
    ,drawno 
  FROM table1 
  UNION ALL 
  SELECT 
    fourth
    ,drawno 
  FROM table1
) AS t GROUP BY drawno;

Two queries, this and from first answer: http://sqlfiddle.com/#!2/2eb93d/2

Best Answer

Related Solutions

Mysql – How to use SUBSTRING using REGEXP in MySQL

MySQL count multiple columns and sum the total occurrence

Related Question