Help with a SQL Query

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 value is the number of points assigned to a vote.

I'd like to be able to SELECT: suggestion_id, title, the number of comments and the SUM of values for that suggestion. sorted by SUM of values. LIMIT 30

Any ideas?

Answer 1

You may want to try using sub queries, as follows:

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 sub queries.

Answer 2

I was typing the same query as potatopeelings.
But there is an issue:
Resultset after joins contains M*N rows (M-number of comments, N-number of votes, not less than 1) for each suggestion.

To avoid that you have to count distinct comment ids and divide a sum of votes by number of comments.

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