busydoingnothing
asked on
Results are multiplied when using SUM() and GROUP BY
I'm attempting to run the following query on a table:
select distinct(batch_number),sum (amount),c ount(*) 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.
select distinct(batch_number),sum
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.
ASKER
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?
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.
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.
You are going to have to post your current output (and include the query used) and desired output.
ASKER
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
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
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.
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.
ASKER
Never figured this one out.
I never got a response to my last comment.
ASKER
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.
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
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
I would recommend 2) Delete/no refund.
ASKER
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).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select
batch_number,
sum(amount),
count(*)
from tablename where
time > '2008-10-22' and time < '2008-10-23'
group by
batch_number