CODEWITHSUNDEEP
mysql
ahren
ahren

Reputation: 16961

MySQL group by affecting other group

I've got a SUM(value) which calculates the votes for each idea, but this gets affected by the amount of tags each idea can have.

For example,

SELECT 
  id, 
  title,
  description,
  COALESCE(SUM(case when value > 0 then value end),0) votes_up,
  COALESCE(SUM(case when value < 0 then value end),0) votes_down,
  GROUP_CONCAT(DISTINCT tags.name) AS 'tags',
FROM ideas
LEFT JOIN votes    ON ideas.id        = votes.idea_id
LEFT JOIN tags_rel ON ideas.id        = tags_rel.idea_id
LEFT JOIN tags     ON tags_rel.tag_id = tags.id
GROUP BY ideas.id

So if there are more than one tags.name, then the SUM() gets multiplied by the number of tags.name

How can I fix this?

Upvotes: 2

Views: 58

Answers (2)

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

SELECT 
  ideas.id, 
  title,
  description,
  COALESCE(SUM(case when value > 0 then value end),0)*t2.cnt votes_up,
  COALESCE(SUM(case when value < 0 then value end),0)*t2.cnt votes_down,
  GROUP_CONCAT(DISTINCT tags.name) AS 'tags'
FROM ideas
LEFT JOIN votes    ON ideas.id        = votes.idea_id
LEFT JOIN tags_rel ON ideas.id        = tags_rel.idea_id
LEFT JOIN tags     ON tags_rel.tag_id = tags.id
LEFT JOIN (SELECT idea_id, COUNT(*) AS cnt 
            FROM tags_rel 
           GROUP BY tags_rel.idea_id) t2 ON ideas.id = t2.idea_id
GROUP BY ideas.id

See SQLFIDDLE

EDIT :

With the tags count column included in the result :

Results:

| ID |  TITLE | DESCRIPTION | VOTES_UP | VOTES_DOWN |                    TAGS | TAGS_COUNT |
|----|--------|-------------|----------|------------|-------------------------|------------|
|  1 | TITLE1 |       DESC1 |       12 |         -4 |             tags1,tags2 |          2 |
|  2 | TITLE2 |       DESC2 |       18 |          0 |       tags4,tags5,tags3 |          3 |
|  3 | TITLE3 |       DESC3 |        0 |        -16 | tags9,tags7,tags8,tags6 |          4 |
|  4 | TITLE4 |       DESC4 |        1 |          0 |                  tags10 |          1 |

Upvotes: 1

Hardy
Hardy

Reputation: 1539

If you don't want your SUM affected with tags, you should not include tags into your SUM query.

I think you can split your query into 2 part (query inside a query) . First to SUM without ideas , then JOIN ideas data for result base on first part

Something would like (sorry but I did not test my query yet but the idea is like that):

SELECT 
  t.id, 
  t.title,
  t.description,
  t.votes_up,
  t.votes_down,
  GROUP_CONCAT(DISTINCT tags.name) AS 'tags',

FROM
(
  SELECT 
    id, 
    title,
    description,
    COALESCE(SUM(case when value > 0 then value end),0) votes_up,
    COALESCE(SUM(case when value < 0 then value end),0) votes_down,
  FROM ideas
  LEFT JOIN votes    ON ideas.id        = votes.idea_id
  GROUP BY ideas.id
) as t
LEFT JOIN tags_rel ON t.id        = tags_rel.idea_id
LEFT JOIN tags     ON tags_rel.tag_id = tags.id

Hope this help

Upvotes: 1

Related Questions