MySQL query with SUM() is not returning the expected results

Question

The query:

SELECT 
    id_data,
    id_tag IN (75) AS tag1,
    id_tag IN (12) AS tag2,
    SUM(id_tag IN (75, 12)) summedTags
FROM
    tags_inservice
WHERE id_service = 1 
GROUP BY id_data 
ORDER BY summedTags DESC

The result:

id_data    tag1    tag2  summedTags
-------  ------  ------  ----------
   3109       0       1           2
   1956       0       0           2
   1928       0       0           1
   2738       1       0           1

What I think is wrong:

The summedTags rarely matches the actual sum of the tags! In the example id_data 3109 has 0 + 1 = 2 which is wrong, and id_data 1956 shows 0 + 0 = 2 which is wrong too, etc, etc. What am I doing wrong?

By the way, I also tried adding HAVING SUM(id_tag IN (75, 12)) = 2 with similar results.

Some more extra info if you need it:

I have a table tags_inservice that contains a bunch of data (represented by id_data, an id from another table) and the data can be tagged by different tags. An id_data can have zero or more id_tags so there could be more than one row with the same id_data value. In fact, sometimes it's valid to have the same id_data tagged with the same id_tag more than once.

DESCRIBE tags_inservice;

Field       Type     Null    Key     Default  Extra         
----------  -------  ------  ------  -------  --------------
id_intag    int(11)  NO      PRI     (NULL)   auto_increment
id_tag      int(11)  YES     MUL     (NULL)                 
id_service  int(11)  YES     MUL     (NULL)                 
data_type   int(11)  YES             (NULL)                 
id_data     int(11)  YES     MUL     (NULL)

Thanks! I hate to read long questions so I tried to look for a way to make it "shorter and sweeter" :) — Julian
– Julian, Commented Aug 2, 2011 at 15:49

ajreal · Accepted Answer · 2011-08-02 16:18:22Z

2

seems the problem is caused by group by as the summedTags is the occurrences of id_data

SELECT 
id_data,
sum(if (id_tag=75,1,0)) as tag1,
sum(if (id_tag=12,1,0)) as tag2,
sum(if (id_tag in(12,75),1,0)) as summedTags
FROM tags_inservice
WHERE id_service = 1 
GROUP BY id_data 
ORDER BY summedTags DESC;

answered Aug 2, 2011 at 16:18

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Julian Over a year ago

This is it! The strange cases I was seeing were those that had a repeated tag. Thanks!

Ryan · Accepted Answer · 2011-08-02 15:31:08Z

0

You could change the summation logic somewhat:

SELECT 
    id_data,
    CASE WHEN id_tag = 75 THEN 1 ELSE 0 END as tag1 , // id_tag IN (75) AS tag1,
    CASE WHEN id_tag = 12 THEN 1 ELSE 0 END AS tag2 , // id_tag IN (12) AS tag2,
    SUM (CASE WHEN id_tag = 75 OR id_tag = 12 THEN 1 ELSE 0 END) AS summedTags
FROM
    tags_inservice
WHERE id_service = 1 
GROUP BY id_data 
ORDER BY summedTags DESC

Not guaranteeing the syntax is correct

answered Aug 2, 2011 at 15:31

Ryan

28.4k10 gold badges58 silver badges83 bronze badges

1 Comment

Julian Over a year ago

as far as syntax the only thing that didn't work was the comments (which I removed) but... I get EXACTLY the same results as with my query :(

Collectives™ on Stack Overflow

MySQL query with SUM() is not returning the expected results

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related