-1

Could you please help to create query which will do next:

I have table

  ID             Amount     Name    
 -----------------------------------
   1              100       Name1                
   2              50        Name2
   3              60        Name3
   4              10        Name4
   5              70        Name5
   6              10        Name6

I need to select all records in any set where sum of amount = 70

Any of results below will correct

1.

  ID             Amount     Name    
 -----------------------------------
   5              70        Name5

2.

  ID             Amount     Name    
 -----------------------------------
   2              50        Name2
   4              10        Name4
   6              10        Name5

3.

  ID             Amount     Name    
 -----------------------------------
   3              60        Name3
   6              10        Name6

4.

  ID             Amount     Name    
 -----------------------------------
   3              60        Name3
   4              10        Name4

Thanks

anx
  • 103
  • 7

1 Answers1

1

I would like to work with first result 

SELECT MAX(ID) [ID], SUM(Amount) [Amount], Name FROM <table>
    GROUP BY Name HAVING SUM(Amount) = 70

EDIT : use of window functions

select ID, Amount, Name from
(
    select ID, 
       Amount,
      Name,
      SUM(Amount) OVER (ORDER BY ID ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) [Sum] from <table>
) a where Sum = 80
Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
  • Sorry, but no. If I use 80 instead of 70 - it should be set of records with IDs 3, 4, 6. But your query return nothing in this case. – anx Nov 29 '17 at 11:19