when sum data in mssql using join three tables , output was not correct

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

SQL

SELECT p.posid,  v.locgrpcde,p.paydte,p.tendercde,
(
    SELECT SUM(p.localamt - (p.changeamt+p.nochgamt)) AS net_amount
)
FROM psbillp p JOIN msPos pv ON p.posid = pv.posid
JOIN msLocation v ON pv.loccde = v.loccde
WHERE v.locgrpcde='GNG10 GROUP' AND paydte='2014-04-01 00:00:00.000'
AND tendercde='CASH'
GROUP BY p.posid, v.locgrpcde,p.paydte,p.tendercde;

result is

G10-POS01 GNG10 GROUP 2014-04-01 00:00:00.000 CASH 121970.00

but i really want value is 118540.00 instead of 121970.00,

Please, can anyone help me?

[edit]
Added from OP "solution"

This is Excel File.

locgrpcde paydte netamount tendercde

GNG10 GROUP 2014-4-1 0:00:00.000 50 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 200 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 180 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 170 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 160 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 300 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 320 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 340 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 350 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 360 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 400 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 380 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 450 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 410 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 500 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 480 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 460 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 550 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 580 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 680 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 700 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 750 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 710 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 800 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 720 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 810 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 900 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 930 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 950 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 730 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 680 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1050 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1150 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1200 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1300 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1450 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1500 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1030 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1510 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1520 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1610 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1750 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1900 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1950 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1980 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1830 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1280 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2130 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2190 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2200 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2130 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2300 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2390 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2640 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2650 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2670 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2970 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 3550 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 3600 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 3680 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 3900 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 3590 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 3150 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 2800 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 1320 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 920 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 5950 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 10000 CASH
GNG10 GROUP 2014-4-1 0:00:00.000 11800 CASH

[/edit]

Posted 18-Oct-15 20:15pm

Member 11213796

Updated 20-Oct-15 20:26pm

OriginalGriff

v2

Add a Solution

Comments

Maciej Los 19-Oct-15 2:41am

Help what? We can not see your data...

Member 11213796 19-Oct-15 4:19am

Dear Sr,
i ran above sql script in my sql2008 R2 , output is "G10-POS01, GNG10 GROUP, 2014-04-01 00:00:00.000, CASH, 121970.00" and i checked excel data but result output was not match. Sum of all columns in excel data was 118540.00 but i received result from sql was 121970.00.
I think some data was added double in sql data and something wrong in sql script at 'sum' . But i can't find error . Please check my sql script.

Shyam S Singh 19-Oct-15 12:26pm

Could you please attached your excel sheet which you are using to run out above query?

Member 11213796 20-Oct-15 2:23am

Dear Sr,
I can't find attach tag . I use sql server 2008 R2 for run above query. I checked manual with excel file for sum data. When i run sql and use SUM method , amount was increase . That it my problem.

Shyam S Singh 20-Oct-15 3:35am

Dear,
I was saying, that attached your excel sheet here which you are using to prepare you data.
Might be there is some hidden rows or column so I need to check.

Member 11213796 21-Oct-15 2:03am

Dear Sir,
I have attached excel file. Please kindly check my excel and script.
In excel, 118540 are receive from netamount column. But in script, 121970 .00 are receive . I really want to amount is 118540. Thanks.

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)

phil.o · Accepted Answer · 2015-10-20T21:07:00

Solution 2

Try

SQL

SELECT
    p.posid
   ,v.locgrpcde
   ,p.paydte
   ,p.tendercde
   ,(p.localamt - p.changeamt - p.nochgamt) AS net_amount
FROM
   psbillp p 
   INNER JOIN msPos pv ON p.posid = pv.posid
   INNER JOIN msLocation v ON pv.loccde = v.loccde
WHERE
   v.locgrpcde = 'GNG10 GROUP'
   AND paydte = '2014-04-01 00:00:00.000'
   AND tendercde = 'CASH'
GROUP BY
    p.posid
   ,v.locgrpcde
   ,p.paydte
   ,p.tendercde;

instead.

Posted 20-Oct-15 21:07pm

phil.o

Comments

Member 11213796 21-Oct-15 5:24am

Dear Sir,
Above script is also ok. But i added p.localamt,p.changeamt and p.nochgamt because 'Msg 8120, Level 16, State 1, Line 8
Column 'psbillp.localamt' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 8
Column 'psbillp.changeamt' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 8
Column 'psbillp.nochgamt' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.'
If i need all sum data in one column (netamount)what can i do. Previous script is can do for all sum data in one column (netamount) but amount is not exactly correct.