Search code examples
sqlsql-serversumaggregatealias

SUM() of calculated field

I have this query:

1)

select 
C.One
,C.Two
,C.Three
,C.Four

from mytable C

where C.One = 11052 and C.Three = 97734

This returns:

One  |Two | Three  | Four
11052   2   97734   4
11052   2   97734   4
11052   2   97734   4
11052   2   97734   4
11052   2   97734   4
11052   2   97734   4
11052   2   97734   4
11052   2   97734   4
11052   2   97734   4
11052   2   97734   4
11052   1   97734   4
11052   1   97734   4
11052   1   97734   4
11052   1   97734   4
11052   1   97734   4
11052   1   97734   4
11052   1   97734   4
11052   1   97734   4
11052   1   97734   4
11052   1   97734   4
11052   3   97734   4
11052   3   97734   4
11052   3   97734   4
11052   3   97734   4
11052   3   97734   4
11052   3   97734   4
11052   3   97734   4
11052   3   97734   4
11052   3   97734   4
11052   3   97734   4
11052   4   97734   4
11052   4   97734   4
11052   4   97734   4
11052   4   97734   4
11052   4   97734   4
11052   4   97734   4
11052   4   97734   4
11052   4   97734   4
11052   4   97734   4
11052   4   97734   4
11052   5   97734   4
11052   5   97734   4
11052   5   97734   4
11052   5   97734   4
11052   5   97734   4
11052   5   97734   4
11052   5   97734   4
11052   5   97734   4
11052   5   97734   4
11052   5   97734   4

Sorry for the long paste, you can see that every row is repeated 10 times for each "Column Two" value (10x for 1, 2, 3, 4, 5) total rows 50.

2)

If I write this:

select 
C.One
,C.Two
,C.Three
,C.Four
,sum(c.Four) as 'Sum'

from mytable C

where C.One = 11052 and C.Three = 97734
group by C.One, C.Two,C.Three , C.Four

Resulting in:

One  | Two | Three  | Four  |  Sum
11052   1       97734   4   40
11052   2       97734   4   40
11052   3       97734   4   40
11052   4       97734   4   40
11052   5       97734   4   40

The Sum should be 20 (4+4+4+4+4) but it shows 40 as the 10x the 4 repeats in the previous query. (Shown above).

I should end up with:

One  | Two | Three  | Four  |  Sum
11052   1       97734   4   20
11052   2       97734   4   20
11052   3       97734   4   20
11052   4       97734   4   20
11052   5       97734   4   20

What should I do to fix this to get 20 and not 40? Thanks for your time.

Solution

  • You can accomplish this with a subquery:

    select a.One,a.Two,a.Three,a.Four,sum(a.Four) as 'Sum'
from (select C.One,C.Two,C.Three,C.Four
  from mytable C
  where C.One = 11052 and C.Three = 97734
  group by C.One, C.Two,C.Three , C.Four) a