Joining tables generating wrong sum amounts

Question

0.00/5 (No votes)

See more:

Hi , I have posted this before, but it was marked resolved and it wasn't resolved, so I am posting again.

I have a query which is as follows:

SQL

SELECT     Prepaid.RedeemerID, OneTimePin.WithdrawerID, SUM(Prepaid.Amount) AS PrepaidAmountSum, SUM(OneTimePin.Amount) AS OneTimePinSum, SUM(Prepaid.Amount)- SUM(OneTimePin.Amount) AS 'Difference'
FROM         Prepaid, OnetimePin
WHERE   (Prepaid.RedeemerID = OneTimePin.WithdrawerID) AND (Prepaid.IsActive = 1) AND (OneTimePin.IsActive = 1) AND (Prepaid.IsRedeemed = 1) AND (OneTimePin.IsPaid = 1)
GROUP BY  Prepaid.RedeemerID,OneTimePin.WithdrawerID
ORDER BY 'Difference', Prepaid.RedeemerID, OneTimePin.WithdrawerID

Posted 10-Mar-14 1:05am

Member 9374423

Updated 10-Mar-14 3:04am

v2

Add a Solution

Comments

DipsMak 10-Mar-14 8:02am

Check the datatype of your columns

DipsMak 10-Mar-14 8:03am

use (SUM(Prepaid.Amount)- SUM(OneTimePin.Amount))

Member 9374423 10-Mar-14 8:08am

Hi, The datatypes are both decimal, and the Sum() is not the issue, it has to be the join, when it sums just the amounts as normal, it almost calculates to all the records in that table, even though the next record is less, so every record sums up almost all the records of the table

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tejas Vaishnav · Accepted Answer · 2014-03-10T02:37:00

Solution 1

can you please try this query..

SQL

SELECT
    Prepaid.RedeemerID,
    OneTimePin.WithdrawerID,
    SUM(ISNULL(Prepaid.Amount,0)) AS PrepaidAmountSum,
    SUM(ISNULL(OneTimePin.Amount,0)) AS OneTimePinSum,
    SUM(ISNULL(Prepaid.Amount,0))- SUM(ISNULL(OneTimePin.Amount,0)) AS 'Difference'
FROM Prepaid
INNER JOIN OnetimePin ON Prepaid.RedeemerID = OneTimePin.WithdrawerID
WHERE  Prepaid.IsActive = 1 AND OneTimePin.IsActive = 1 AND Prepaid.IsRedeemed = 1 AND OneTimePin.IsPaid = 1
GROUP BY  Prepaid.RedeemerID,OneTimePin.WithdrawerID
ORDER BY 'Difference', Prepaid.RedeemerID, OneTimePin.WithdrawerID

Posted 10-Mar-14 2:37am

Tejas Vaishnav

Comments

Member 9374423 10-Mar-14 8:54am

Hi, thank you for the response, however the same issue still occurs...

Tejas Vaishnav 10-Mar-14 8:58am

can you please share your data, like what is selection output, and what is your desired output, and also some short of your Prepaid and Onetime Pin table actual data..

just some short of rows, so it will giving except idea, where is your query is breaking...

Use improve question functionality to add your data...

Member 9374423 10-Mar-14 9:01am

please see the updated question, kind regards

Tejas Vaishnav 10-Mar-14 9:17am

you have posted just tow column data for both the table, what about IsActive or IsPaid something like this...

Anyway no need to do that, you got your issue because of your where cause...

WHERE Prepaid.IsActive = 1 AND OneTimePin.IsActive = 1 AND Prepaid.IsRedeemed = 1 AND OneTimePin.IsPaid = 1

Can you please try the above query with modified where cause like this...

WHERE Prepaid.RedeemerID = 21689

Member 9374423 10-Mar-14 9:50am

Hi The specified redeemerID is just an example of one record that returns in the output,

Tejas Vaishnav 11-Mar-14 8:34am

so can you please try like something

WHERE Prepaid.RedeemerID IN (21689,21681,21682,21683)

pass your RedeemerID with IN cause to check that what is the sum of that Ids..