Solved

Results are multiplied when using SUM() and GROUP BY

Posted on 2008-10-24
15
591 Views
Last Modified: 2010-07-27
I'm attempting to run the following query on a table:

select distinct(batch_number),sum(amount),count(*) from tablename where
time > '2008-10-22' and time < '2008-10-23'
group by batch_number

It appears the results are multiplied greatly. The maximum count(*) I should get is 280, but I'm getting 1120, 560, etc. If I remove the distinct(batch_number) and group by batch_number, I get the correct totals. The problem is, I need that batch_number to know which batch has which amounts. This is for Microsoft SQL Server.
0
Comment
Question by:busydoingnothing
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
15 Comments
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22801949
isnt this what u need ? just remove distinct from select

select
 batch_number,
 sum(amount),
 count(*)
from tablename where
time > '2008-10-22' and time < '2008-10-23'
group by
 batch_number
0
 

Author Comment

by:busydoingnothing
ID: 22801988
I've tried that. I still get multiplied records in certain groups. I'm not sure if there's maybe other criteria that's causing it to count it several times over?
0
 
LVL 16

Expert Comment

by:Steve Krile
ID: 22803269
I just tried something similar.  I would make a couple changes to your syntax.  Try this:

Select
   batch_number,
   sum(amount),
   count(batch_number)
from
   tablename
where
   Time between '10/22/2008' and '10/23/2008'
group by
    batch_number



I have a similar table where I've done this and it works fine.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22804752
You are going to have to post your current output (and include the query used) and desired output.
0
 

Author Comment

by:busydoingnothing
ID: 22814785
Here's the query used:

select batch, sum(amount), count(batch) from incomingchecks where
timereceived between '2008-10-22' and '2008-10-23'
group by batch order by batch

The first row in the results comes back as this:

30684      828.92000      16

When I run the same query and specify the specific batch number by running this:

select fk_batch, sum(amount), count(pk_incomingcheck) from incomingchecks where
fk_batch=30684 group by fk_Batch

I get the following, correct output:

30684      207.23000      4
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22818254
Not surprising.  In the first case you are GROUPing by batch.  In the second by fk_Batch.  What do you get when you run this:

select      fk_batch,
      sum(amount),
      count(*)
from      incomingchecks
where      timereceived between '2008-10-22' and '2008-10-23'
      And fk_batch = 30684
group by
      fk_Batch

If the answer is not:
30684      207.23000      4

Than you know that the cause of the difference has to lie with the timereceived condition.
0
 

Author Comment

by:busydoingnothing
ID: 23457906
Never figured this one out.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 23458247
I never got a response to my last comment.
0
 

Author Comment

by:busydoingnothing
ID: 23458359
That was a typo on my part. fk_batch is the actual name of the column, I just shortened up the query for posting purposes.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 23458373
So again, what do you get when you execute this:
select      fk_batch,
      sum(amount),
      count(*)
from      incomingchecks
where      timereceived between '2008-10-22' and '2008-10-23'
      And fk_batch = 30684
group by
      fk_Batch
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 23504641
I would recommend 2) Delete/no refund.
0
 

Author Comment

by:busydoingnothing
ID: 23505069
I tried acperkins' suggestion and it still multiplied. I ended up running the query again using a different datetime column in the table and that appeared to work. There was only a single date and no time in that column versus the other column, which had a date and time. I wonder if the presence of the time caused it to be multiplied by the number of unique times that were there (i.e. if there were 100 instances of 12:30 and 200 instances of 1:30, the results would be multiplied by 2 because there were two unique times).
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 125 total points
ID: 23510772
Right that is what I said:
"If the answer is not:
30684      207.23000      4

Than you know that the cause of the difference has to lie with the timereceived condition."

Incidentally, this:
"There was only a single date and no time in that column "
Is not correct.  A datetime column always has time in it.  It is just that in your particular case it was set to midnight.
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question