Avatar of Phil Chapman
Phil ChapmanFlag for United States of America asked on

MSSQL Query

Issue:
A person has several invoice that may have one or more transactions in a Transaction file.
What I need to do is get the transactions ID where some of the transactions equal a specified amount.

Sample Transaction Table Fields

Transaction_ID
TranDate
InvoiceNo
Amount

The query below would give me what I need IF IT WOULD ONLY WORK

SELECT Transaction_ID
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367) AND
SUM(Amount) = 2388.60

PLEASE HELP I NEED TO GET THE Transaction_ID of those transaction where the sum = 2388.60
SQL

Avatar of undefined
Last Comment
Pawan Kumar

8/22/2022 - Mon
Pawan Kumar

Please try this like this ..Added GROUP BY and Having clause.

SELECT Transaction_ID
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367) 
GROUP BY Transaction_ID
HAVING SUM(Amount) = 2388.60

Open in new window

ASKER
Phil Chapman

I don't get any errors but the query should return 2 Transactions_ID and it's not returning anything.
Pawan Kumar

Can you please give me output of this -

SELECT Transaction_ID , SUM(Amount) amt 
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367) 
GROUP BY Transaction_ID 

Open in new window

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
Phil Chapman

There is 1 transaction for 1,390.60 and 1 for 998.00
Pawan Kumar

HAVING SUM(Amount) = 2388.60

so because of above it did not return anything.

is this amount - 2388.60 for single transaction ?
ASKER
Phil Chapman

SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367)
GROUP BY Transaction_ID

Where do you enter the amount you are looking for?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Pawan Kumar

We can do one of these below

SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367)
GROUP BY Transaction_ID
HAVING SUM(Amount) = someValue

OR

SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367)  AND Amount = someValue
GROUP BY Transaction_ID
ASKER
Phil Chapman

No there is more than 1 transaction that equals 2388.60
Pawan Kumar

can you please give me output of this - ( copy paste - do not change anything. )

SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367)
GROUP BY Transaction_ID
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Pawan Kumar

I think there is some decimal issue is there.

Do we have any values from 2388.60.. like 2388.6000002.. ??

Please try like this also..

SELECT Transaction_ID , SUM(Amount) amt 
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367) 
GROUP BY Transaction_ID
HAVING SUM(Amount) >= 2388 AND SUM(Amount) < 2389

Open in new window

ASKER
Phil Chapman

SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367)
GROUP BY Transaction_ID
HAVING SUM(Amount) = 2388.6

RTURNS NO RECORDS
ASKER
Phil Chapman

SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367)  AND Amount = 2388.6
GROUP BY Transaction_ID

RETURNS NO RECORDS
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Pawan Kumar

Can you please give me output of below ??

SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367)
GROUP BY Transaction_ID
ASKER
Phil Chapman

The amount we are looking for is 2388.60  I've tried 2388.60  and 2388.6
Pawan Kumar

Data Please ??

Hv u tried below -

SELECT Transaction_ID , SUM(Amount) amt 
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367) 
GROUP BY Transaction_ID
HAVING SUM(Amount) >= 2388 AND SUM(Amount) < 2389

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
Phil Chapman

When I run the query below in Microsoft SQL Server Management Studio

SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367)
GROUP BY Transaction_ID


The results is the Headers
Transaction_ID and amt
but no data
Pawan Kumar

Please run below -

SELECT * FROM AccountsTransactions

and copy all the data with headers into excel and attach. I will check for you.
ASKER
Phil Chapman

SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367)
GROUP BY Transaction_ID
HAVING SUM(Amount) >= 2388 AND SUM(Amount) < 2389

SAME THING HEADERS ONLY
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Pawan Kumar

Please run below -

SELECT * FROM AccountsTransactions

and copy all the data with headers into excel and attach. I will check for you.
ASKER
Phil Chapman

See image file attached
Query-2017-11-24_11-24-58.jpg
Pawan Kumar

You are doing 1390.60 + 998 ?? This is incorrect as their Transaction_IDs are different --- > 198188 , 198189 so the sum will not happen.

Below will work without any issues.

SELECT Transaction_ID , SUM(Amount) amt
FROM AccountsTransactions
WHERE Invoice IN (106263,106262,106367,106367)
GROUP BY Transaction_ID
HAVING SUM(Amount) = 1390.60
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
Phil Chapman

The query will have to sum the amounts and give me the Transaction_ID of the ones that totals the requested amount.

When the query is run I will not know what the lowest or highest amount is.
ASKER
Phil Chapman

There could be 30 or 40 transactions and it might take 6 or 7 to make up the amount requested.
ASKER CERTIFIED SOLUTION
Pawan Kumar

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Phil Chapman

Ok, Thanks for the help
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Pawan Kumar

welcome. glad to help as always.