# question on query with sum using 5 tables

• ### 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

• 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
• 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
• 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

### 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
• 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
• 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
• 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
• 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
• tnx i guess id better sort to making separate queries to avoid problems
Friday, March 25, 2011 3:09 AM