Mysql8 join and count unique real appearances-CodePudding

I have the following talbes:

CREATE TABLE topics (
  id INT,
  text VARCHAR(100),
  parent VARCHAR(1)
);

CREATE TABLE sentiment (
  id INT,
  grade INT,
  parent VARCHAR(1)
);

And the following data:

INSERT INTO topics (id, text, parent) VALUES (1, 'Cryptocurrency', 'A');
INSERT INTO topics (id, text, parent) VALUES (2, 'Cryptocurrency', 'B');
INSERT INTO topics (id, text, parent) VALUES (2, 'ETH', 'B');

INSERT INTO sentiment (id, grade, parent) VALUES (2, 0 , 'A');
INSERT INTO sentiment (id, grade, parent) VALUES (2, 1 , 'A');
INSERT INTO sentiment (id, grade, parent) VALUES (2, 1 , 'A');
INSERT INTO sentiment (id, grade, parent) VALUES (2, 1 , 'A');
INSERT INTO sentiment (id, grade, parent) VALUES (2, 0 , 'B');
INSERT INTO sentiment (id, grade, parent) VALUES (2, 1 , 'B');

I want to select count of each topics.text and shared parent sum of sentiment.grade.
So I came up with the following query:

SELECT 
    count(topics.text), 
    topics.text,
    sum(sentiment.grade)
  
   FROM topics
   inner join sentiment on (sentiment.parent = topics.parent)
   group by text

The result:


| count(topics.text) | sum(sentiment.grade) | text           |
| ------------------ | -------------------- | -------------- |
| 6                  | 4                    | Cryptocurrency |
| 2                  | 1                    | ETH            |

---

I only have a problem with the first column, the real count of Cryptocurrency is 2 and the real count of ETH is 1.

Can you fix this query?
(I'm using mysql8, would be glad to have 5.7 compliant if possible)

CodePudding user response：

As you have two rows with text=cryptocurrency in topics, one with parent=A and the other with parent=B, when you join you should expect to see 6 rows for crpytocurrency(the first row of topics matches the first four of sentiment, and the second row of topics matches the last two of sentiment). You can see that if you change your original query to this one:

SELECT 
   *
   FROM topics
   inner join sentiment on (sentiment.parent = topics.parent)

I guess you want to see the number of topics with the same text and the total grades their parents have (for cryptocurrency, the sum of A and B). This could help you:

SELECT 
    topics_count.n_text,
    topics.text,
    SUM(sentiment.grade)
    
   FROM topics
   INNER JOIN (SELECT text, count(*) 'n_text' FROM topics GROUP BY text) topics_count ON topics.text = topics_count.text
   INNER JOIN sentiment ON (sentiment.parent = topics.parent)
GROUP BY text