Joining tables

I have two tables one table T1 only have one result per date table 2 T2 can
range from 0 to 8 results per date,
I want to pull back all of the results in T2 and sum the totals, in my
example it will be summing "Stafftime" and "items", in T1 I need to sum the
"Contrctaed_Hours" and the OverTime. when I do sum the contracted Time I am
getting 61200, as there ate 2 rows in T1.
How can I sum my four colums but only get results for one Date in the T1
table'
Cheers
Mark
Select *
From T1
T1
Row_Date Contracted_Hours OverTime Ext_No
20050209 30600 0 4227
Select *
From T2
T2
RowDate Area Ext_No StaffTime Items
20050209 1 4227 24656 50
20050209 2 4227 6164 10
Select T1.RowDate, T1.Ext_No, sum(Contracted_Hours) as Con_Hrs,
Sum(OverTime) as OT, Sum(Items) as Completed_Work
From T1
Inner Join T2
On T1.Ext_No = T2.Ext_No
Where RowDate = '20050209' and Ext = 4227Try,
Select
T1.RowDate,
T1.Ext_No,
sum(distinct T1.Contracted_Hours) as Con_Hrs,
Sum(distinct T1.OverTime) as OT,
(select Sum(T2.Items) from T2 where T2.RowDate = T1.RowDate and T1.Ext_No =
T2.Ext_No) as Completed_Work
From
T1
Where
T1.RowDate = '20050209' and T1.Ext_No = 4227
go
AMB
"sh0t2bts" wrote:

> I have two tables one table T1 only have one result per date table 2 T2 ca
n
> range from 0 to 8 results per date,
> I want to pull back all of the results in T2 and sum the totals, in my
> example it will be summing "Stafftime" and "items", in T1 I need to sum th
e
> "Contrctaed_Hours" and the OverTime. when I do sum the contracted Time I a
m
> getting 61200, as there ate 2 rows in T1.
>
> How can I sum my four colums but only get results for one Date in the T1
> table'
>
> Cheers
> Mark
> Select *
> From T1
> T1
> Row_Date Contracted_Hours OverTime Ext_No
> 20050209 30600 0 4227
> Select *
> From T2
> T2
> RowDate Area Ext_No StaffTime Items
> 20050209 1 4227 24656 50
> 20050209 2 4227 6164 10
> Select T1.RowDate, T1.Ext_No, sum(Contracted_Hours) as Con_Hrs,
> Sum(OverTime) as OT, Sum(Items) as Completed_Work
> From T1
> Inner Join T2
> On T1.Ext_No = T2.Ext_No
> Where RowDate = '20050209' and Ext = 4227
>
>|||I think you have to use LEFT OUTER JOIN.
"sh0t2bts" wrote:

> I have two tables one table T1 only have one result per date table 2 T2 ca
n
> range from 0 to 8 results per date,
> I want to pull back all of the results in T2 and sum the totals, in my
> example it will be summing "Stafftime" and "items", in T1 I need to sum th
e
> "Contrctaed_Hours" and the OverTime. when I do sum the contracted Time I a
m
> getting 61200, as there ate 2 rows in T1.
>
> How can I sum my four colums but only get results for one Date in the T1
> table'
>
> Cheers
> Mark
> Select *
> From T1
> T1
> Row_Date Contracted_Hours OverTime Ext_No
> 20050209 30600 0 4227
> Select *
> From T2
> T2
> RowDate Area Ext_No StaffTime Items
> 20050209 1 4227 24656 50
> 20050209 2 4227 6164 10
> Select T1.RowDate, T1.Ext_No, sum(Contracted_Hours) as Con_Hrs,
> Sum(OverTime) as OT, Sum(Items) as Completed_Work
> From T1
> Inner Join T2
> On T1.Ext_No = T2.Ext_No
> Where RowDate = '20050209' and Ext = 4227
>
>|||SELECT
T1.RowDate,
T1.Ext_No,
T1.Contracted_Hours,
T1.OverTime,
T2.StaffTime,
T2.Items
FROM
T1
INNER JOIN
(
SELECT
T2.RowDate,
T2.Ext_No,
SUM(StaffTime),
SUM(Items)
FROM
T2
GROUP BY
T2.RowDate,
T2.Ext_No
) T2group
ON T2group.Ext_No = T1.Ext_No
AND T2group.RowDate = T1.Row_Date
"sh0t2bts" wrote:

> I have two tables one table T1 only have one result per date table 2 T2 ca
n
> range from 0 to 8 results per date,
> I want to pull back all of the results in T2 and sum the totals, in my
> example it will be summing "Stafftime" and "items", in T1 I need to sum th
e
> "Contrctaed_Hours" and the OverTime. when I do sum the contracted Time I a
m
> getting 61200, as there ate 2 rows in T1.
>
> How can I sum my four colums but only get results for one Date in the T1
> table'
>
> Cheers
> Mark
> Select *
> From T1
> T1
> Row_Date Contracted_Hours OverTime Ext_No
> 20050209 30600 0 4227
> Select *
> From T2
> T2
> RowDate Area Ext_No StaffTime Items
> 20050209 1 4227 24656 50
> 20050209 2 4227 6164 10
> Select T1.RowDate, T1.Ext_No, sum(Contracted_Hours) as Con_Hrs,
> Sum(OverTime) as OT, Sum(Items) as Completed_Work
> From T1
> Inner Join T2
> On T1.Ext_No = T2.Ext_No
> Where RowDate = '20050209' and Ext = 4227
>
>