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

Answer 1

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

Answer 2

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)

Answer 3

@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

Answer 4

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

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

tnx i guess id better sort to making separate queries to avoid problems