CODEWITHSUNDEEP
mysql
user783322
user783322

Reputation: 481

MySQL: IF statement used with SUM

I have the following table:

Unique | Common |  Score
1      |   1    |   10  
2      |   1    |   10  
3      |   1    |   10
4      |   1    |   -10

If I run this:

SELECT
    SUM(`score`) AS `total`
FROM
    `test`
GROUP BY
    `common`

I get the total:

20

This is what I'd expect.

I want to treat negative numbers as zero and sum the remaining score to get a total. So, if I run this:

SELECT
    IF(`score`<0, 0, SUM(`score`)) AS `total`
FROM
    `myTable`
GROUP BY
    `unique`

I get these totals:

10
10
10
0

This is what I'd expect as well.

But, If I run this:

SELECT
    IF(`score`<0, 0, SUM(`score`)) AS `total`
FROM
    `myTable`
GROUP BY
    `common`

I get a total of:

20

Why do I get 20 instead of 30 when performing the last query? The only difference is the grouping.

BTW, removing the superflous Group by clause from the last query also gives a result of 20.

Thanks in advance.

Upvotes: 1

Views: 4604

Answers (3)

Mike Chamberlain
Mike Chamberlain

Reputation: 407

Try 

SELECT
    SUM( IF(`score`<0, 0, score))  AS `total`
FROM
    `myTable`
GROUP BY
    `common`

Upvotes: 0

liquorvicar
liquorvicar

Reputation: 6106

I haven't tested this but I think you have your IF and SUM the wrong way round. I think it should be like this

SELECT
  SUM( IF(`score`<0, 0,`score`) ) AS `total`
FROM
  `myTable`
GROUP BY
  `common`

Upvotes: 3

safarov
safarov

Reputation: 7804

Try this

 SELECT
    SUM(`score`) AS `total`
FROM
 `myTable` WHERE score > 0
GROUP BY
 `common`

Upvotes: 3

Related Questions