Post reply

number of rows returned different when applying joins

  • March 31, 2010 at 7:17 pm

    #222729

    HI,

    I have two tables emp1,emp2 both having field as id.Both have 6 rows already present.

    When i wright this

    select cast(sum(emp1.id)/avg(emp1.id) as numeric) from emp1

    this returns 6 which is the number of rows present (all not null rows).

    Similarly for this query

    select cast(sum(emp2.id)/avg(emp2.id) as numeric) from emp2

    returns 6 which is the number of rows.

    But when i wright this code

    select cast(sum(emp1.id)/avg(emp1.id) as numeric) from emp1,emp2

    this returns 37 rows ,instead of 36 rows.

    I checked this query also

    select count(emp1.id) from emp1,emp2

    which is giving 36. So, why in second last query number returned is 37? Mathematically, sum/average of a series of number gives the count of total number of numbers present.

  • March 31, 2010 at 8:30 pm

    #1143602

    My guess would be "rounding". Post up your table definitions and your data and we can prove or disprove that.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • March 31, 2010 at 8:39 pm

    #1143605

    Amended, with apologies to anyone who read the first draft.

    It's a rounding error (of sorts) assuming the IDs are integers.

    create table emp1 (id int)

    create table emp2 (id int)

    insert into emp1

    select 1 union all

    select 2 union all

    select 3 union all

    select 4 union all

    select 5 union all

    select 6

    insert into emp2

    select * from emp1

    select * from emp1

    select * from emp2

    select sum(emp1.id) as sumID, avg(emp1.id) as avgID

    ,cast(sum(emp1.id)/avg(emp1.id) as numeric) as calculationResult

    ,COUNT(*) as rowsCount

    from emp1,emp2

    The sum is 126, the average is 3, and the calcResult is therefore 42.

    The number of rows is 36. BUT...

    select sum(emp1.id) as sumID, avg(1.0*emp1.id) as avgID

    ,cast(sum(emp1.id)/avg(1.0*emp1.id) as numeric) as calculationResult

    ,COUNT(*) as rowsCount

    from emp1,emp2

    The sum is 126, the average is 3.5, and the calculated result is 36.

    In SQL, when you take the average of an integer column, the result is returned as an integer. Specifying 1.0*emp1.id changes the datatype being averaged and the result is calculated correctly.

    You may find it helpful to look at the rules concerning data type precedence in SQL calculations in Books Online (BOL).

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • March 31, 2010 at 9:43 pm

    #1143635

    create table emp1(id int);

    create table emp2(id int);

    values in both emp1 emp2 are inserted via "insert into table values(value)" command.

    data are 10,11,12,13,14,15

  • April 1, 2010 at 7:30 am

    #1143952

    Thanks for taking the time to send in your actual data. Plug those values into my example(s) above and you should see exactly what is happening.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • April 2, 2010 at 4:20 pm

    #1145141

    Missread the original post..

  • April 2, 2010 at 7:11 pm

    #1145187

    Thanks a lot

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply