Group by / distinct combined with sum?

Question

Hello to all!

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

Using a subselect and distinct:

  select sum(valform) from (
    select distinct a.id, a.valfrom ...
    inner join ...
    where ...
  )

Or using group-by:

  select sum(valform) from (
    select a.id, min(a.valfrom)
    inner join ...
    where ...
    group by a.id
  )

But I reckon the first query will be faster.

Answer 3

This will do the trick :)

select a.id, a.valfrom, SUM(val) as mySum ...
  inner join ...
  where ...
GROUP BY NULL

Answer 4

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.