find sum of n rows values

Question

I need get the invoice number which has the particular value in price column, if not all the invoice numbers which have the sum value of that particular value in price column ..how to write an sql query for that

i have attached the data of my table Data

invoice     price
v1        	1000      
v2        	200       
v3        	35        
v4        	500       
v5        	50        
v6        	300       
v7	        400
.            .
.            '
.            .
vn          34

expected output : if(input 500) found =>then found v4

else(sum of(v2,v6)

query with multiple join

select d1.invoice, d1.price,
       (case when d2.invoice <> d1.invoice then d2.invoice end) as invoice2,
       (case when d2.invoice <> d1.invoice then d2.price end) as price2
from data d1 left join
     data d2
     on d1.invoice <= d2.invoice
where (d1.price = 500 and d2.invoice = d1.invoice) or
      (d1.price + d2.price) = 500

my current query is not giving dynamic results, suppose the input is 735,so the result should be sum of 3 columns sum of(v2,v3,v4), in that case I have to tweak your query. can you suggest me a way to dynamic it .thanks in advance .

Answer 1

You can try this below logic which will return you all 3 invoice number v2,v4&v6.

v2 as Price = 500

V4 and v6 for SUM of their price = 500

You can make some changes if the requirement if different. But I this the following logic will help you.

DEMO HERE

SELECT A.invoice
FROM your_table A
CROSS APPLY your_table B
WHERE A.invoice = B.invoice AND A.Price = 500 -- This will check the price as exact input or not
OR (
    A.invoice <> B.invoice AND A.Price + B.Price = 500
    -- This will check sum of 2 different invoice is same as input or not
)