Group by / distinct combined with sum?

Question

I have a sql statement like this:

select a.id, a.valfrom ...
  inner join ...
  where ...;

As a result I have this:

id    val
---------
 3     10
 3     10
 3     10
 9     21
 9     21
11      2
11      2
13     30

So you can see, one id has one value.

If I do a group by (a.id), I get:

id    val
---------
 3     10
 9     21
11      2
13     30

What I want to get of the last result is the sum: 10+21+2+30 = 63.

So how can I get the sum as a single result? If I do a sum(a.val) and use group by (a.id) I do not get 63, I get the sum for every id, for example id=3 -> 10+10+10 = 30.

Best Regards.

Answer 1

You don't want a GROUP BY, then. You also can't select the ID correctly in standard SQL. You just want:

SELECT SUM(val) FROM (SELECT DISTINCT id, val FROM ...) AS foo

But, MySQL supports a few extensions to standard SQL syntax which MIGHT make this work:

SELECT DISTINCT id, SUM(val) FROM ...

Answer 2

If you query is

select a.id, a.valfrom ...
  inner join ...
  where ...;

try this:

select sum(distinct a.valfrom)
  inner join ...
  where ...;

No "group by", hands down.