RHebel February 2016

SQL Server SUM based on subsequent records

Microsoft SQL Server 2012 (SP1) - 11.0.3156.0 (X64)

I am not sure of the best way to word this and have tried a few different searches with different combinations of words without success.

I only want to Sum Sequence = 1 when there are Sequence > 1, in the table below the Sequence = 1 lines marked with *. I don't care at all about checking that Sequence 2,3,etc match the same pattern because if they exist at all I need to Sum them.

I have data that looks like this:

| Sequence | ID | Num | OtherID |
|----------|----|-----|---------|
|        1 |  1 |  10 |       1 |*
|        2 |  1 |  15 |       1 |
|        3 |  1 |  20 |       1 |
|        1 |  2 |  10 |       1 |*
|        2 |  2 |  15 |       1 |
|        1 |  3 |  10 |       1 |
|        1 |  1 |  40 |       3 |

I need to sum the Num column but only when there is more than one sequence. My output would look like this:

Sequence    Sum  OtherID
   1         20     1
   2         30     1
   3         20     1

I have tried grouping the queries in a bunch of different ways but really by the time I get to the sum, I don't know how to look ahead to make sure there are greater than 1 sequences for an ID.

My query at the moment looks something like this:

select Sequence, Sum(Num) as [Sum], OtherID 
from tbl 
where ID in (Select ID from tbl where Sequence > 1)
Group by Sequence, OtherID

tbl is a CTE that I wrapped around my query and it partially works, but is not really the filter I wanted.

If this is something that just shouldn't be done or can't be done then I can handle that, but if it's something I am missing I'd like to fix the query.

Edit:

I can't give the full query here but I started with this table/data (to get the above output). The OtherID is there because the

Answers


Wyatt Shipman February 2016

It looks like you are trying to sum by Sequence and OtherID if the Count of ID >1, so you could do something like below:

select Sequence, Sum(Num) as [Sum], OtherID 
from tbl 
where ID in (Select ID from tbl where Sequence > 1)
Group by Sequence, OtherID
Having count(id)>1


BeanFrog February 2016

The following will sum over Sequence and OtherID, but only when: Either

  • sequence is greater than 1

or

  • there is something else with the same ID and OtherID, but a different sequence.

Query:

select Sequence, Sum(Num) as SumNum, OtherID from @tmpTable a
where Sequence > 1 
      or exists (select * from @tmpTable b 
                 where a.ID = b.ID 
                   and a.OtherID = b.OtherID 
                   and b.Sequence <> a.Sequence)
group by Sequence, OtherID;

Post Status

Asked in February 2016
Viewed 2,938 times
Voted 9
Answered 2 times

Search




Leave an answer