none
question on query with sum using 5 tables RRS feed

  • Question

  • SELECT     schid.SchID, schid.SchName, SUM(tbl_firstyear.male) AS m1, SUM(tbl_firstyear.female) AS f1, SUM(tbl_secondyear.male) AS m2,
                          SUM(tbl_secondyear.female) AS f2, SUM(tbl_thirdyear.male) AS m3, SUM(tbl_thirdyear.female) AS f3, SUM(tbl_fourthyear.male) AS m4,
                          SUM(tbl_fourthyear.female) AS f4
    FROM         schid INNER JOIN
                          tbl_firstyear ON schid.SchID = tbl_firstyear.schoolid INNER JOIN
                          tbl_fourthyear ON schid.SchID = tbl_fourthyear.schoolid INNER JOIN
                          tbl_secondyear ON schid.SchID = tbl_secondyear.schoolid INNER JOIN
                          tbl_thirdyear ON schid.SchID = tbl_thirdyear.schoolid
    GROUP BY schid.SchID, schid.SchName

    here is the syntax im wondering why the query returning a sum amounting to thousands example the male column and the female column only consist of numbers ranging from 1 to 50 can somebody give an answer..tnx

    Wednesday, March 23, 2011 3:48 AM

Answers

  • Here is a simple sample demonstrating the problem you get with SUM when multiple tables are involved:

    use tempdb 
    
    create table #Parent (Id int identity(1,1) primary key, [Name] varchar(10))
    insert into #Parent values ('Parent1'),('Parent2')
    
    create table #Child1 (Id int identity(1,1) primary key, [ParentID] int, [Name] varchar(10), 
    CONSTRAINT fk_Parent FOREIGN KEY (ParentId)
    REFERENCES #Parent(Id) )
    
    insert into #Child1 values (1,'Child1'),(1,'Child2'),(2,'Ch1'),(2,'Ch2'), (2,'Ch3')
    
    create table #Child2 (Id int identity(1,1) primary key, [ParentID] int, [Name] varchar(10), 
    CONSTRAINT fk_Parent FOREIGN KEY (ParentId)
    REFERENCES #Parent(Id) )
    
    insert into #Child2 values (1,'SChild1'),(1,'SChild2'),(1,'SChild3'),(2,'SCh1'),(2,'SCh2')
    
    select P.*, C1.[Name] as [Child1], C2.[Name] as Child2
    from #Parent P inner join #Child1 C1 on P.Id = C1.ParentID 
    inner join #Child2 C2 on P.ID = C2.ParentID 
    
    select P.*, count(C1.[Name]) as [Child1Cnt], count(C2.[Name]) as Child2Cnt
    from #Parent P inner join #Child1 C1 on P.Id = C1.ParentID 
    inner join #Child2 C2 on P.ID = C2.ParentID 
    group by P.Id, P.Name 
    
    -- correct sum
    select P.*, C1.ChildCnt1, C2.ChildCnt2 from #Parent P 
    INNER JOIN (select ParentID, COUNT(Name) as ChildCnt1 from #Child1 group by ParentID) C1 
    on P.Id = C1.ParentID 
    INNER JOIN (select ParentID, COUNT(Name) as ChildCnt2 from #Child2 group by ParentID) C2
    on P.Id = C2.ParentID 
    
    


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, March 23, 2011 1:20 PM
    Moderator
  • Yes, you can not use aggregate functions when more than 1 table is involved as I demonstrated with the query above. Use the technique which I labeled correct sum (derived tables).
    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, March 24, 2011 12:18 AM
    Moderator
  • Any aggregate function result will be wrong in multi-join situation as I already explained. I showed a sample based on count, but avg or SUM will produce the same problem.
    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, March 24, 2011 1:57 AM
    Moderator

All replies

  • Have you checked the data? Also, what do you have in these columns and do you really want to sum that column or count?

    I also would probably write this query differently doing one SUM at a time and not all 4 JOINs at once as in this case the results may be wrong. It's better to do it separately.


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, March 23, 2011 4:46 AM
    Moderator
  • Hi,

    Your syntax seems right, may be underlying data in table is such...

    You can look into the tables and see if that is the case..


    - Chintak (My Blog)

    Wednesday, March 23, 2011 4:46 AM
  • @naomi:

    what is the use of the join statement if i cant join 4 tables you are right i made a 4 separate query and the results are accurate but what difference would it make if i join 4 tables instead of doing 4 separate queries

     

    @chintak

    hi the female and male column on all the tables have the same data type they are all integer i don't know why its returning a sum of thousands

    Wednesday, March 23, 2011 4:53 AM
  • I know that you can not use SUM if you're using JOINs. I can show you some samples later on that explains this problem. I was hit with the exact same problem couple of years ago, so I learned this rule that time.
    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, March 23, 2011 4:57 AM
    Moderator
  • i did use join in my 4 seperate queries i join the tbl_firstyear and the table schid to be able to group them then return a sum with the name and the id on the table schid i just cant understand why when joining more than 2 tables it results became in accurate
    Wednesday, March 23, 2011 5:01 AM
  • Here is a simple sample demonstrating the problem you get with SUM when multiple tables are involved:

    use tempdb 
    
    create table #Parent (Id int identity(1,1) primary key, [Name] varchar(10))
    insert into #Parent values ('Parent1'),('Parent2')
    
    create table #Child1 (Id int identity(1,1) primary key, [ParentID] int, [Name] varchar(10), 
    CONSTRAINT fk_Parent FOREIGN KEY (ParentId)
    REFERENCES #Parent(Id) )
    
    insert into #Child1 values (1,'Child1'),(1,'Child2'),(2,'Ch1'),(2,'Ch2'), (2,'Ch3')
    
    create table #Child2 (Id int identity(1,1) primary key, [ParentID] int, [Name] varchar(10), 
    CONSTRAINT fk_Parent FOREIGN KEY (ParentId)
    REFERENCES #Parent(Id) )
    
    insert into #Child2 values (1,'SChild1'),(1,'SChild2'),(1,'SChild3'),(2,'SCh1'),(2,'SCh2')
    
    select P.*, C1.[Name] as [Child1], C2.[Name] as Child2
    from #Parent P inner join #Child1 C1 on P.Id = C1.ParentID 
    inner join #Child2 C2 on P.ID = C2.ParentID 
    
    select P.*, count(C1.[Name]) as [Child1Cnt], count(C2.[Name]) as Child2Cnt
    from #Parent P inner join #Child1 C1 on P.Id = C1.ParentID 
    inner join #Child2 C2 on P.ID = C2.ParentID 
    group by P.Id, P.Name 
    
    -- correct sum
    select P.*, C1.ChildCnt1, C2.ChildCnt2 from #Parent P 
    INNER JOIN (select ParentID, COUNT(Name) as ChildCnt1 from #Child1 group by ParentID) C1 
    on P.Id = C1.ParentID 
    INNER JOIN (select ParentID, COUNT(Name) as ChildCnt2 from #Child2 group by ParentID) C2
    on P.Id = C2.ParentID 
    
    


    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, March 23, 2011 1:20 PM
    Moderator
  • so to sum it up you can't sum a column with more than two tables involved?i made a query consisting of two tables

    example table

    tbl 1

    schoolid - pk

    school_name

    tbl 2

    id - pk

    schoolid

    male

    female

     

    i have join the two tables to return a sum of the male and female column it returned accurately but when i try to join more than those mention then the problem arises

    Thursday, March 24, 2011 12:04 AM
  • Yes, you can not use aggregate functions when more than 1 table is involved as I demonstrated with the query above. Use the technique which I labeled correct sum (derived tables).
    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, March 24, 2011 12:18 AM
    Moderator
  • im not looking to count but what i want is to sum but well if thats the case might as well create 4 query to get away from the problem
    Thursday, March 24, 2011 12:53 AM
  • Any aggregate function result will be wrong in multi-join situation as I already explained. I showed a sample based on count, but avg or SUM will produce the same problem.
    For every expert, there is an equal and opposite expert. - Becker's Law

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Thursday, March 24, 2011 1:57 AM
    Moderator
  • tnx i guess id better sort to making separate queries to avoid problems
    Friday, March 25, 2011 3:09 AM