It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes sql problem - sum is Incorrect Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "sql problem - sum is Incorrect" Watch "sql problem - sum is Incorrect" New topic
Author

sql problem - sum is Incorrect

Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
can anyone show me the light here.

this statment produce the wrong sum (chgTotal) SUM(unit * charge)
- it duplicates the sum for some reason. why?






thanks
Paul Clapham
Sheriff

Joined: Oct 14, 2005
Posts: 19728
    
  10

Then the query without the GROUP BY clause returns twice as many rows as you think it should. So you need more criteria in your WHERE clause to stop that from happening. Run the query without GROUP BY (and without SUM and MIN and MAX) and maybe you'll see why it is producing the duplicate records.
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 20078
    
  30

Probably in your join, you have two records of one table mapping to one record of the other table. The join creates two records in the result set, even though there is only one record in the one table.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6 - OCEJPAD 6
How To Ask Questions How To Answer Questions
 
I’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link: http://aspose.com
 
subject: sql problem - sum is Incorrect
 
It's not a secret anymore!