Solved

Results are multiplied when using SUM() and GROUP BY

Posted on 2008-10-24
15
589 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
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

770 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