Group differently combined with the amount?

Question

I have a sql statement:

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

As a result, I have the following:

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

So, you can see, one identifier has one value.

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

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

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

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

Regards.

Answer 1

Then you do not want GROUP BY. You also cannot correctly select the identifier in standard SQL. You just want to:

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

MySQL SQL, :

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

Answer 2

:

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

:

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

, .

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 request

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

try the following:

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

No "group by," hands down.

Answer 5

You just need to use Distinct on which you have to summarize.

select ID,Sum(Distinct Val) 
from Table
Group By ID;