SUM() gone wrong.

14 Jul 2006 4:19 PM

wmureports

Ok, so I have a table that looks like this

SessionID    CourseID    Users
       1              10           100
       1              10            105
       2              15           100
       3              20            102
       3              20            100

Now what I want to do is Group the sessions with the same course ID's
and sum up the users and make it look like

SessionID    CourseID   Users
    1                10             205
    2                 15            100
    3                 20           202

Pretty simple right?

So i write query similar to

Select SessionID, CourseID, Sum(users) as TotalUsers
from Courses
Group by SessionID, CourseID
Order by SessionID

My problem arises when check my output... The user totals are NO WHERE
NEAR accurate. They are way higher than they should be. For session 1
the total would be something like 500.
Has anyone ever encounted this sort of problem?

(this is a real numbed down version of what im actually doing, but its
the general jist if what I am doing and trying to accomplish)

14 Jul 2006 4:49 PM

Arnie Rowland

For the query and data provided, the results are as you would expect. That
you are getting something different causes me to suspect the nature of the
data.

Are you doing some form or JOIN in the query whereby there could be row
duplication?
Are there undiscovered data anomolies?

Are you counting the same rows? (Add a count(1) to the query and compare the
number of rows to the result set with the aggregates and GROUP BY removed.)

It would be useful to see the NOT 'real numbed down version ' of the query
(most of the folks here are not real 'numb' -although there are days...)
Also the table DDL and data in the form of INSERT statements would allow us
to attempt to recreate the issue and help you come up with an understanding
and solution. Without that, we are just playing 20 questions. (And I've
already asked 4 of them.)

--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."

Show quote

"wmureports" <justin.a.mo***@gmail.com> wrote in message
news:1152893971.324253.69600@m73g2000cwd.googlegroups.com...
> Ok, so I have a table that looks like this
>
> SessionID    CourseID    Users
>       1              10           100
>       1              10            105
>       2              15           100
>       3              20            102
>       3              20            100
>
> Now what I want to do is Group the sessions with the same course ID's
> and sum up the users and make it look like
>
> SessionID    CourseID   Users
>    1                10             205
>    2                 15            100
>    3                 20           202
>
> Pretty simple right?
>
> So i write query similar to
>
> Select SessionID, CourseID, Sum(users) as TotalUsers
> from Courses
> Group by SessionID, CourseID
> Order by SessionID
>
> My problem arises when check my output... The user totals are NO WHERE
> NEAR accurate. They are way higher than they should be. For session 1
> the total would be something like 500.
> Has anyone ever encounted this sort of problem?
>
> (this is a real numbed down version of what im actually doing, but its
> the general jist if what I am doing and trying to accomplish)
>

14 Jul 2006 4:50 PM

Tim Dot NoSpam

You must have something else going on like a cross-product.

using your example below, I created this:

create table #tmp1 (
courseId int,
sessionId int,
userEmail int
)
insert #tmp1
select 1, 10, 100
union
select 1, 10, 105
union
select 2,15,100
union
select 3,20,102
union select 3,20,100

select CourseId, Sessionid, SUM(userEmail)
from #tmp1
group by CourseId,SessionId
order by sessionId

I got your desired response.

What is your specifc query and what do the tables look like?

-Tim

Show quote

"wmureports" <justin.a.mo***@gmail.com> wrote in message news:1152893971.324253.69600@m73g2000cwd.googlegroups.com...
> Ok, so I have a table that looks like this
>
> SessionID    CourseID    Users
>       1              10           100
>       1              10            105
>       2              15           100
>       3              20            102
>       3              20            100
>
> Now what I want to do is Group the sessions with the same course ID's
> and sum up the users and make it look like
>
> SessionID    CourseID   Users
>    1                10             205
>    2                 15            100
>    3                 20           202
>
> Pretty simple right?
>
> So i write query similar to
>
> Select SessionID, CourseID, Sum(users) as TotalUsers
> from Courses
> Group by SessionID, CourseID
> Order by SessionID
>
> My problem arises when check my output... The user totals are NO WHERE
> NEAR accurate. They are way higher than they should be. For session 1
> the total would be something like 500.
> Has anyone ever encounted this sort of problem?
>
> (this is a real numbed down version of what im actually doing, but its
> the general jist if what I am doing and trying to accomplish)
>