SQL query help

Question

My tables:

suggestions:
suggestion_id|title|description|user_id|status|created_time

suggestion_comments:
scomment_id|text|user_id|suggestion_id

suggestion_votes:
user_id|suggestion_id|value

Where the value is the number of points assigned to vote.

I would like to be able to SELECT: suggestion_id, title, number of comments and SUM values ​​for this sentence. sorted by SUM values. LIMIT 30

Any ideas?

Answer 1

You can try using subqueries as shown below:

SELECT  s.suggestion_id,
        (   
           SELECT COUNT(*) 
           FROM suggestion_comments sc 
           WHERE sc.suggestion_id = s.suggestion_id
        ) num_of_comments,
        (   
           SELECT SUM(sv.value) 
           FROM suggestion_votes sv
           WHERE sv.suggestion_id = s.suggestion_id
        ) sum_of_values
FROM    suggestions s;

Test case:

CREATE TABLE suggestions (suggestion_id int);
CREATE TABLE suggestion_comments (scomment_id int, suggestion_id int);
CREATE TABLE suggestion_votes (user_id int, suggestion_id int, value int);

INSERT INTO suggestions VALUES (1);
INSERT INTO suggestions VALUES (2);
INSERT INTO suggestions VALUES (3);

INSERT INTO suggestion_comments VALUES (1, 1);
INSERT INTO suggestion_comments VALUES (2, 1);
INSERT INTO suggestion_comments VALUES (3, 2);
INSERT INTO suggestion_comments VALUES (4, 2);
INSERT INTO suggestion_comments VALUES (5, 2);
INSERT INTO suggestion_comments VALUES (6, 3);

INSERT INTO suggestion_votes VALUES (1, 1, 3);
INSERT INTO suggestion_votes VALUES (2, 1, 5);
INSERT INTO suggestion_votes VALUES (3, 1, 1);
INSERT INTO suggestion_votes VALUES (1, 2, 4);
INSERT INTO suggestion_votes VALUES (2, 2, 2);
INSERT INTO suggestion_votes VALUES (1, 3, 5);

Result:

+---------------+-----------------+---------------+
| suggestion_id | num_of_comments | sum_of_values |
+---------------+-----------------+---------------+
|             1 |               2 |             9 |
|             2 |               3 |             6 |
|             3 |               1 |             5 |
+---------------+-----------------+---------------+
3 rows in set (0.00 sec)

---

UPDATE: @ Naktibalda's solution is an alternative solution that avoids subqueries.

Answer 2

, .
:
Resultset M * N (M- , N- , 1) .

, .

SELECT 
    s.*, 
    COUNT(DISTINCT c.scommentid) AS comment_count,
    SUM(v.value)/GREATEST(COUNT(DISTINCT c.scommentid), 1) AS total_votes
FROM suggestions AS s
LEFT JOIN suggestion_comments AS c ON s.suggestion_id = c.suggestion_id 
LEFT JOIN suggestion_votes AS v ON s.suggestion_id = v.suggestion_id
GROUP BY s.suggestion_id
ORDER BY total_votes DESC
LIMIT 30