>Hi,
>
>I am having a problem Joining Statement into my main query when summing
>the results, what happens is in the original query I get a result of 2
>when included in the main query in increases to 12, Eh!!
>
>Here is the query I am joining in:-
>Select Distinct UserName, Convert(Char(10),AssessmentDate,121) as
>AssessmentDate, Count(distinct ID) as NoMOT, Avg(FinalScore) As
>AvgMOTScore
>>From OC5MOT.dbo.MOTResults
>Where Convert(Char(8), AssessmentDate, 112) Between '20060701' and
>'20060731'
>And AssessmentBy in ('Manager')
>Group by UserName, Convert(Char(10),AssessmentDate,121)
>
>Which gives me the following result:-
>UserName       Date         Count            Average
>Allan Stone    2006-07-14    2    69.93
>Gemma Fletcher    2006-07-14    2    79.34
>Kay Kendall    2006-07-14    2    89.46
>
>Which is exacly what I am expecting and is correct, I am using a Left
>Outer Join to connect it to my Main query, as below:-
>Select distinct AWD.EmpName, AWD.TimesheetDate,
>
>IsNull(NoMOT,0) as NoMOT, IsNull(AvgMOTScore,0) as AvgMOTScore
>
>>From AgentWorkDetail AWD
>Left Outer Join (
>Select Distinct UserName, Convert(Char(10),AssessmentDate,121) as
>AssessmentDate, Count(distinct ID) as NoMOT, Avg(FinalScore) As
>AvgMOTScore
>>From OC5MOT.dbo.MOTResults
>Where Convert(Char(8), AssessmentDate, 112) Between '20060712' and
>'20060715'
>And AssessmentBy in ('JReid')
>Group by UserName, Convert(Char(10),AssessmentDate,121)
>) As MOT on MOT.UserName = EmpName and MOT.AssessmentDate =
>Convert(Char(10),AWD.TimeSheetDate,121)
>Where Convert(Char(8), TimeSheetDate, 112) Between '20060712' and
>'20060715'
>And SupNTName in ('Manager')
>Group By AWD.EmpName, TimesheetDate, NoMOT, AvgMOTScore
>Order By AWD.EmpName
>
>When i use this "IsNull(NoMOT,0) as NoMOT, IsNull(AvgMOTScore,0) as
>AvgMOTScore" in the Select statment I get the Following Result:-
>UserName        Date            Count         Avgerage
>Allan Stone  2006-07-12 00:00:00.000    0    0.0
>Allan Stone  2006-07-13 00:00:00.000    0    0.0
>Allan Stone  2006-07-14 00:00:00.000    2    69.93
>Allan Stone  2006-07-15 00:00:00.000    0    0.0
>Gemma Goose 2006-07-12 00:00:00.000    0    0.0
>Gemma Goose 2006-07-13 00:00:00.000    0    0.0
>Gemma Goose 2006-07-14 00:00:00.000    2    79.34
>Gemma Goose 2006-07-15 00:00:00.000    0    0.0
>Kay Kendall 2006-07-12 00:00:00.000    0    0.0
>Kay Kendall 2006-07-13 00:00:00.000    0    0.0
>Kay Kendall 2006-07-14 00:00:00.000    2    89.46
>Kay Kendall 2006-07-15 00:00:00.000    0    0.0
>
>Which is fine but I now want it as one row, so I take
>"AWD.TimesheetDate," out of the select statment and sum the NoMOT and
>Avgrage the AvgMOTScore Like so
>"IsNull(sum(NoMOT),0) as NoMOT, IsNull(Avg(AvgMOTScore),0) as
>AvgMOTScore" and take the same fields out of the Groupb By clause,
>Thanks you for reading this far, here comes the best bit as a result i
>get:-
>Name         Count       Average
>Allan Stone    12    69.93
>Gemma Goose    2    79.34
>Kay Water    2    89.46
>
>
>I am really confused now, how come allan's count increases to 12?
>as Allan's count increases how come the other two dont?
>
>I just dont get it.
>
>Thanks for reading this I really hope you can help..
>
>Many thanks
>
>Mark
>
> 
>

> Mark,
>
> Perhaps you have more than one row in AgentWorkDetail for
> Allan Stone on July 14? When you got this row in your result:
>
> Allan Stone  2006-07-14 00:00:00.000    2    69.93
>
>
> it could have come from a group of 6 rows in the left outer
> join before DISTINCT.  (Your use of DISTINCT in more places
> than seems needed is a red flag.)
>
> Allan Stone  2006-07-14 00:00:00.000    2    69.93
> Allan Stone  2006-07-14 00:00:00.000    2    69.93
> Allan Stone  2006-07-14 00:00:00.000    2    69.93
> Allan Stone  2006-07-14 00:00:00.000    2    69.93
> Allan Stone  2006-07-14 00:00:00.000    2    69.93
> Allan Stone  2006-07-14 00:00:00.000    2    69.93
>
>
> I think you are assuming (and maybe it is supposed to be
> the case, but there are insufficient data integrity constraints
> in place to maintain it) that the join on
>   on MOT.UserName = EmpName
>   and MOT.AssessmentDate = Convert(Char(10),AWD.TimeSheetDate,121)
> will only yield one row for each (UserName , AssessmentDate) combination.
> If the primary key of AgentWorkDetail is (EmpName, TimesheetDate), it
> will, but it if the primary key of AgentWorkDetail is something like
> (EmpName, TimesheetDate, CostCenter), it may not.
>
> Steve Kass
> Drew University
> www.stevekass.com
>
> Sh0t2bts wrote:
>
> >Hi,
> >
> >I am having a problem Joining Statement into my main query when summing
> >the results, what happens is in the original query I get a result of 2
> >when included in the main query in increases to 12, Eh!!
> >
> >Here is the query I am joining in:-
> >Select Distinct UserName, Convert(Char(10),AssessmentDate,121) as
> >AssessmentDate, Count(distinct ID) as NoMOT, Avg(FinalScore) As
> >AvgMOTScore
> >>From OC5MOT.dbo.MOTResults
> >Where Convert(Char(8), AssessmentDate, 112) Between '20060701' and
> >'20060731'
> >And AssessmentBy in ('Manager')
> >Group by UserName, Convert(Char(10),AssessmentDate,121)
> >
> >Which gives me the following result:-
> >UserName       Date         Count            Average
> >Allan Stone    2006-07-14    2    69.93
> >Gemma Fletcher    2006-07-14    2    79.34
> >Kay Kendall    2006-07-14    2    89.46
> >
> >Which is exacly what I am expecting and is correct, I am using a Left
> >Outer Join to connect it to my Main query, as below:-
> >Select distinct AWD.EmpName, AWD.TimesheetDate,
> >
> >IsNull(NoMOT,0) as NoMOT, IsNull(AvgMOTScore,0) as AvgMOTScore
> >
> >>From AgentWorkDetail AWD
> >Left Outer Join (
> >Select Distinct UserName, Convert(Char(10),AssessmentDate,121) as
> >AssessmentDate, Count(distinct ID) as NoMOT, Avg(FinalScore) As
> >AvgMOTScore
> >>From OC5MOT.dbo.MOTResults
> >Where Convert(Char(8), AssessmentDate, 112) Between '20060712' and
> >'20060715'
> >And AssessmentBy in ('JReid')
> >Group by UserName, Convert(Char(10),AssessmentDate,121)
> >) As MOT on MOT.UserName = EmpName and MOT.AssessmentDate =
> >Convert(Char(10),AWD.TimeSheetDate,121)
> >Where Convert(Char(8), TimeSheetDate, 112) Between '20060712' and
> >'20060715'
> >And SupNTName in ('Manager')
> >Group By AWD.EmpName, TimesheetDate, NoMOT, AvgMOTScore
> >Order By AWD.EmpName
> >
> >When i use this "IsNull(NoMOT,0) as NoMOT, IsNull(AvgMOTScore,0) as
> >AvgMOTScore" in the Select statment I get the Following Result:-
> >UserName        Date            Count         Avgerage
> >Allan Stone  2006-07-12 00:00:00.000    0    0.0
> >Allan Stone  2006-07-13 00:00:00.000    0    0.0
> >Allan Stone  2006-07-14 00:00:00.000    2    69.93
> >Allan Stone  2006-07-15 00:00:00.000    0    0.0
> >Gemma Goose 2006-07-12 00:00:00.000    0    0.0
> >Gemma Goose 2006-07-13 00:00:00.000    0    0.0
> >Gemma Goose 2006-07-14 00:00:00.000    2    79.34
> >Gemma Goose 2006-07-15 00:00:00.000    0    0.0
> >Kay Kendall 2006-07-12 00:00:00.000    0    0.0
> >Kay Kendall 2006-07-13 00:00:00.000    0    0.0
> >Kay Kendall 2006-07-14 00:00:00.000    2    89.46
> >Kay Kendall 2006-07-15 00:00:00.000    0    0.0
> >
> >Which is fine but I now want it as one row, so I take
> >"AWD.TimesheetDate," out of the select statment and sum the NoMOT and
> >Avgrage the AvgMOTScore Like so
> >"IsNull(sum(NoMOT),0) as NoMOT, IsNull(Avg(AvgMOTScore),0) as
> >AvgMOTScore" and take the same fields out of the Groupb By clause,
> >Thanks you for reading this far, here comes the best bit as a result i
> >get:-
> >Name         Count       Average
> >Allan Stone    12    69.93
> >Gemma Goose    2    79.34
> >Kay Water    2    89.46
> >
> >
> >I am really confused now, how come allan's count increases to 12?
> >as Allan's count increases how come the other two dont?
> >
> >I just dont get it.
> >
> >Thanks for reading this I really hope you can help..
> >
> >Many thanks
> >
> >Mark
> >
> > 
> >

On 4 Aug 2006 03:57:40 -0700, "Sh0t2bts" <sh0t2***@hotmail.com> wrote:

>Hi,
>
>I am having a problem Joining Statement into my main query when summing
>the results, what happens is in the original query I get a result of 2
>when included in the main query in increases to 12, Eh!!
>
>Here is the query I am joining in:-
>Select Distinct UserName, Convert(Char(10),AssessmentDate,121) as
>AssessmentDate, Count(distinct ID) as NoMOT, Avg(FinalScore) As
>AvgMOTScore
>>From OC5MOT.dbo.MOTResults
>Where Convert(Char(8), AssessmentDate, 112) Between '20060701' and
>'20060731'
>And AssessmentBy in ('Manager')
>Group by UserName, Convert(Char(10),AssessmentDate,121)
>
>Which gives me the following result:-
>UserName       Date         Count            Average
>Allan Stone    2006-07-14    2    69.93
>Gemma Fletcher    2006-07-14    2    79.34
>Kay Kendall    2006-07-14    2    89.46
>
>Which is exacly what I am expecting and is correct, I am using a Left
>Outer Join to connect it to my Main query, as below:-
>Select distinct AWD.EmpName, AWD.TimesheetDate,
>
>IsNull(NoMOT,0) as NoMOT, IsNull(AvgMOTScore,0) as AvgMOTScore
>
>>From AgentWorkDetail AWD
>Left Outer Join (
>Select Distinct UserName, Convert(Char(10),AssessmentDate,121) as
>AssessmentDate, Count(distinct ID) as NoMOT, Avg(FinalScore) As
>AvgMOTScore
>>From OC5MOT.dbo.MOTResults
>Where Convert(Char(8), AssessmentDate, 112) Between '20060712' and
>'20060715'
>And AssessmentBy in ('JReid')
>Group by UserName, Convert(Char(10),AssessmentDate,121)
>) As MOT on MOT.UserName = EmpName and MOT.AssessmentDate =
>Convert(Char(10),AWD.TimeSheetDate,121)
>Where Convert(Char(8), TimeSheetDate, 112) Between '20060712' and
>'20060715'
>And SupNTName in ('Manager')
>Group By AWD.EmpName, TimesheetDate, NoMOT, AvgMOTScore
>Order By AWD.EmpName
>
>When i use this "IsNull(NoMOT,0) as NoMOT, IsNull(AvgMOTScore,0) as
>AvgMOTScore" in the Select statment I get the Following Result:-
>UserName        Date            Count         Avgerage
>Allan Stone  2006-07-12 00:00:00.000    0    0.0
>Allan Stone  2006-07-13 00:00:00.000    0    0.0
>Allan Stone  2006-07-14 00:00:00.000    2    69.93
>Allan Stone  2006-07-15 00:00:00.000    0    0.0
>Gemma Goose 2006-07-12 00:00:00.000    0    0.0
>Gemma Goose 2006-07-13 00:00:00.000    0    0.0
>Gemma Goose 2006-07-14 00:00:00.000    2    79.34
>Gemma Goose 2006-07-15 00:00:00.000    0    0.0
>Kay Kendall 2006-07-12 00:00:00.000    0    0.0
>Kay Kendall 2006-07-13 00:00:00.000    0    0.0
>Kay Kendall 2006-07-14 00:00:00.000    2    89.46
>Kay Kendall 2006-07-15 00:00:00.000    0    0.0
>
>Which is fine but I now want it as one row, so I take
>"AWD.TimesheetDate," out of the select statment and sum the NoMOT and
>Avgrage the AvgMOTScore Like so
>"IsNull(sum(NoMOT),0) as NoMOT, IsNull(Avg(AvgMOTScore),0) as
>AvgMOTScore" and take the same fields out of the Groupb By clause,
>Thanks you for reading this far, here comes the best bit as a result i
>get:-
>Name         Count       Average
>Allan Stone    12    69.93
>Gemma Goose    2    79.34
>Kay Water    2    89.46
>
>
>I am really confused now, how come allan's count increases to 12?
>as Allan's count increases how come the other two dont?
>
>I just dont get it.
>
>Thanks for reading this I really hope you can help..
>
>Many thanks
>
>Mark

> I see that Steve already fixed it, but I just spent all this time
> writing this so I might as well post it!    8-)
>
> I don't have the answer, but I do have a few points.
>
> Most of the time when numbers in the result set grow like that the
> problem is with a join.  Imagine joining on only LastName when the
> join should have been on LastName and FirstName.  If there is only one
> Harvey it would join as a one-to-one.  If there are three Smiths it
> would match three-to-three.  Another join problem with this symptom is
> when a table is involved in more than a single one-to-many
> relationship; imagine an Employee joined to a set of dependents from
> one table, and a set of benefits from another table.  With two
> dependents, and medical, dental and life insurance, the result would
> be six rows.
>
> (It is worth mentioning, but not applicable to your problem, that one
> of the warning signs of join problems, is DISTINCT.  There are
> perfectly legitimate uses for DISTINCT, and even cases where it does
> nothing at all (see below), but I have too often seen it used to "fix"
> a problem resulting from incorrect joins.)
>
> With SELECT DISTINCT...GROUP BY the DISTINCT is redundant.  GROUP BY
> already makes the results DISTINCT.  Get rid of it.
>
> When your SELECT list has:
>        IsNull(NoMOT,0) as NoMOT,
>        IsNull(AvgMOTScore,0) as AvgMOTScore
> but the GROUP BY has:
>  GROUP BY AWD.EmpName, NoMOT, AvgMOTScore
> the references to NoMOT and AvgMOTScore in the GROUP BY are to the
> original columns, not the ISNULLed versions.  It is a better practice
> to use the EXACT expression from the SELECT list in the GROUP BY.
>
> When it came to the final SELECT, I tried to apply the changes you
> described but could not make sense of it all.  The query being
> modified had NoMOT and AvgMOTScore in the GROUP BY.  You described
> changing the SELECT list so that they were SUMMED, but specified no
> change to the GROUP BY.  Perhaps posting the complete problem query
> would help.
>
> Roy Harvey
> Beacon Falls, CT1
>
> On 4 Aug 2006 03:57:40 -0700, "Sh0t2bts" <sh0t2***@hotmail.com> wrote:
>
> >Hi,
> >
> >I am having a problem Joining Statement into my main query when summing
> >the results, what happens is in the original query I get a result of 2
> >when included in the main query in increases to 12, Eh!!
> >
> >Here is the query I am joining in:-
> >Select Distinct UserName, Convert(Char(10),AssessmentDate,121) as
> >AssessmentDate, Count(distinct ID) as NoMOT, Avg(FinalScore) As
> >AvgMOTScore
> >>From OC5MOT.dbo.MOTResults
> >Where Convert(Char(8), AssessmentDate, 112) Between '20060701' and
> >'20060731'
> >And AssessmentBy in ('Manager')
> >Group by UserName, Convert(Char(10),AssessmentDate,121)
> >
> >Which gives me the following result:-
> >UserName       Date         Count            Average
> >Allan Stone    2006-07-14    2    69.93
> >Gemma Fletcher    2006-07-14    2    79.34
> >Kay Kendall    2006-07-14    2    89.46
> >
> >Which is exacly what I am expecting and is correct, I am using a Left
> >Outer Join to connect it to my Main query, as below:-
> >Select distinct AWD.EmpName, AWD.TimesheetDate,
> >
> >IsNull(NoMOT,0) as NoMOT, IsNull(AvgMOTScore,0) as AvgMOTScore
> >
> >>From AgentWorkDetail AWD
> >Left Outer Join (
> >Select Distinct UserName, Convert(Char(10),AssessmentDate,121) as
> >AssessmentDate, Count(distinct ID) as NoMOT, Avg(FinalScore) As
> >AvgMOTScore
> >>From OC5MOT.dbo.MOTResults
> >Where Convert(Char(8), AssessmentDate, 112) Between '20060712' and
> >'20060715'
> >And AssessmentBy in ('JReid')
> >Group by UserName, Convert(Char(10),AssessmentDate,121)
> >) As MOT on MOT.UserName = EmpName and MOT.AssessmentDate =
> >Convert(Char(10),AWD.TimeSheetDate,121)
> >Where Convert(Char(8), TimeSheetDate, 112) Between '20060712' and
> >'20060715'
> >And SupNTName in ('Manager')
> >Group By AWD.EmpName, TimesheetDate, NoMOT, AvgMOTScore
> >Order By AWD.EmpName
> >
> >When i use this "IsNull(NoMOT,0) as NoMOT, IsNull(AvgMOTScore,0) as
> >AvgMOTScore" in the Select statment I get the Following Result:-
> >UserName        Date            Count         Avgerage
> >Allan Stone  2006-07-12 00:00:00.000    0    0.0
> >Allan Stone  2006-07-13 00:00:00.000    0    0.0
> >Allan Stone  2006-07-14 00:00:00.000    2    69.93
> >Allan Stone  2006-07-15 00:00:00.000    0    0.0
> >Gemma Goose 2006-07-12 00:00:00.000    0    0.0
> >Gemma Goose 2006-07-13 00:00:00.000    0    0.0
> >Gemma Goose 2006-07-14 00:00:00.000    2    79.34
> >Gemma Goose 2006-07-15 00:00:00.000    0    0.0
> >Kay Kendall 2006-07-12 00:00:00.000    0    0.0
> >Kay Kendall 2006-07-13 00:00:00.000    0    0.0
> >Kay Kendall 2006-07-14 00:00:00.000    2    89.46
> >Kay Kendall 2006-07-15 00:00:00.000    0    0.0
> >
> >Which is fine but I now want it as one row, so I take
> >"AWD.TimesheetDate," out of the select statment and sum the NoMOT and
> >Avgrage the AvgMOTScore Like so
> >"IsNull(sum(NoMOT),0) as NoMOT, IsNull(Avg(AvgMOTScore),0) as
> >AvgMOTScore" and take the same fields out of the Groupb By clause,
> >Thanks you for reading this far, here comes the best bit as a result i
> >get:-
> >Name         Count       Average
> >Allan Stone    12    69.93
> >Gemma Goose    2    79.34
> >Kay Water    2    89.46
> >
> >
> >I am really confused now, how come allan's count increases to 12?
> >as Allan's count increases how come the other two dont?
> >
> >I just dont get it.
> >
> >Thanks for reading this I really hope you can help..
> >
> >Many thanks
> >
> >Mark