Win a copy of Java Mock Exams (software) this week in the Programmer Certification (OCPJP) forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

sql problem - sum is Incorrect

 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 21955
36
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 20827
68
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Happiness is not a goal ... it's a by-product of a life well lived - Eleanor Roosevelt. Tiny ad:
the new thread boost feature: great for the advertiser and smooth for the coderanch user
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!