•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 402,048 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,537 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser: Programming Forums
Views: 537 | Replies: 3
 |
•
•
Join Date: Jul 2008
Posts: 2
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
Hi all!
Ive created this code to read in data from several tables but i have a problem with SUM. With this code im trying to add together all the null values that have the most recent date but all the SUM commandi have does is multiply the number by 3 :x
This is the output i get, instead of adding together 961.8 and 1274.52, it just multiplys them
NULL - 961.8 - 2885.4
NULL - 1274.52 - 3823.56
All help appreciated
Ive created this code to read in data from several tables but i have a problem with SUM. With this code im trying to add together all the null values that have the most recent date but all the SUM commandi have does is multiply the number by 3 :x
SELECT A.REFNO, MAX(B.AMT), C.STATUS, C.ENDD, C.PAMT, SUM(C.PAMT) FROM BASIC A LEFT JOIN TableB B ON B.REFNO = PD.REFNO AND A.BGROUP = B.BGROUP LEFT JOIN TableC ON A.REFNO = C.REFNO AND A.BGROUP = PH1.BGROUP LEFT JOIN TableC C ON B.REFNO = PS1.REFNO AND A.BGROUP = C.BGROUP WHERE A.REFNO = 'A170580' AND C.ENDD IS NULL GROUP BY A.REFNO, C.STATUS, C.ENDD, C.PAMT
This is the output i get, instead of adding together 961.8 and 1274.52, it just multiplys them
NULL - 961.8 - 2885.4
NULL - 1274.52 - 3823.56
All help appreciated
Last edited by chris-uk-lad : Jul 22nd, 2008 at 3:44 pm.
•
•
Join Date: Apr 2008
Posts: 295
Reputation:
Rep Power: 1
Solved Threads: 41
Posting Whiz in Training
hi,
there are some discrepancies:
1. What are PD.REFNO, PH1.BGROUP, PS1.REFNO ?
2. Is there a self-join: LEFT JOIN TableC LEFT JOIN TableC C ???
3. TableC of first left join doesn't have alias.
4. Select has 6 columns but only 3 results are showing.
Btw, threefold results may likely occur by adding same values three times. Such stuff happens if joining conditions are wrongly chosen what then results in undesirable cross products (cartesian products).
-----
tesu
there are some discrepancies:
1. What are PD.REFNO, PH1.BGROUP, PS1.REFNO ?
2. Is there a self-join: LEFT JOIN TableC LEFT JOIN TableC C ???
3. TableC of first left join doesn't have alias.
4. Select has 6 columns but only 3 results are showing.
Btw, threefold results may likely occur by adding same values three times. Such stuff happens if joining conditions are wrongly chosen what then results in undesirable cross products (cartesian products).
-----
tesu
Information is moving—you know, nightly news is one way, of course, but it's also moving through the blogosphere and through the Internets. I promise you I will listen to what has been said here, even though I wasn't here. Ann and I will carry out this equivocal message to the world. I'm the master of low expectations.
•
•
Join Date: Jul 2008
Posts: 2
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
My apologies, i changed some of the syntax to make it more readable, hence the discrepencies you outline, guess i fail lol. This is the unaltered code.
I was only showing that output as a sample as only the final value is whats affected, so when End Date is Null in several rows, the PAMT values are added together
SELECT B.REFNO, MAX(PH1.PH02D), PS1.STATUS, PS1.ENDD, SUM(PS1.PAMT) FROM BASIC B LEFT JOIN Pre Date PD ON B.REFNO = PD.REFNO AND B.BGROUP = PD.BGROUP LEFT JOIN Pre Holding PH1 ON B.REFNO = PH1.REFNO AND B.BGROUP = PH1.BGROUP LEFT JOIN Pre Sum PS1 ON B.REFNO = PS1.REFNO AND B.BGROUP = PS1.BGROUP WHERE B.REFNO = 'A170580' AND PS1.ENDD IS NULL GROUP BY B.REFNO, PS1.STATUS, PS1.ENDD, PS1.PAMT
I was only showing that output as a sample as only the final value is whats affected, so when End Date is Null in several rows, the PAMT values are added together
•
•
Join Date: Apr 2008
Posts: 295
Reputation:
Rep Power: 1
Solved Threads: 41
Posting Whiz in Training
And also that "unaltered" code went through some modification meanwhile, LOL
Information is moving—you know, nightly news is one way, of course, but it's also moving through the blogosphere and through the Internets. I promise you I will listen to what has been said here, even though I wasn't here. Ann and I will carry out this equivocal message to the world. I'm the master of low expectations.
|
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
- How to covert this sql statement to array (PHP)
- SQL SERVER 2000 Selecting a record based on an aggregate function (MS SQL)
- Syntax for adding two columns and sorting the results (MySQL)
- SUM From Two Tables (VB.NET)
- Problem with query. (MS SQL)
- VB and Excel. (Visual Basic 4 / 5 / 6)
- SQL does not substract (MS SQL)
Other Threads in the MS SQL Forum
- Previous Thread: Adding quotes to a textfile using sql
- Next Thread: How to prevent hacking the databas by entering javascript
