Search code examples
sqlsql-serversql-server-2000

How to get value without subqueries (on SQL-Server)?

I have the following table on SQL Server:

ID   Num
 1     A
 2     B
 2     B
 3     C
 3     C
 4     C

(Num is a numeric column - A, B, and C are standins for numeric values, for the purpose of this question)

How to get the value of A+B+C+C without using subqueries and CTE?

A - for 1, B - for 2, C - for 3, C - for 4.

The answer seems to sum(distinct Num), but distinct is by ID field!

Demo table:

create table test (ID int, Num int);
insert into test values (1, 10);
insert into test values (2, 100);
insert into test values (2, 100);
insert into test values (3, 1000);
insert into test values (3, 1000);
insert into test values (4, 1000);

The correct answer is 10+100+1000+1000 = 2110.

Solution

  • The solution:

    select cast(sum(distinct Num + cast(0.00001 as number(38,19))/ID) as number(18,2))