Answered by:
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.SchNamehere 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- Proposed as answer by Stephanie Lv Wednesday, March 30, 2011 9:12 AM
- Marked as answer by Alex Feng (SQL) Wednesday, March 30, 2011 9:25 AM
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- Proposed as answer by Stephanie Lv Wednesday, March 30, 2011 1:27 AM
- Marked as answer by Alex Feng (SQL) Wednesday, March 30, 2011 9:25 AM
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- Proposed as answer by Stephanie Lv Wednesday, March 30, 2011 1:27 AM
- Marked as answer by Alex Feng (SQL) Wednesday, March 30, 2011 9:25 AM
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 blogWednesday, 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 blogWednesday, 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 accurateWednesday, 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- Proposed as answer by Stephanie Lv Wednesday, March 30, 2011 9:12 AM
- Marked as answer by Alex Feng (SQL) Wednesday, March 30, 2011 9:25 AM
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- Proposed as answer by Stephanie Lv Wednesday, March 30, 2011 1:27 AM
- Marked as answer by Alex Feng (SQL) Wednesday, March 30, 2011 9:25 AM
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 problemThursday, 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- Proposed as answer by Stephanie Lv Wednesday, March 30, 2011 1:27 AM
- Marked as answer by Alex Feng (SQL) Wednesday, March 30, 2011 9:25 AM
Thursday, March 24, 2011 1:57 AM -
tnx i guess id better sort to making separate queries to avoid problemsFriday, March 25, 2011 3:09 AM