April 27, 2008 at 2:18 am
This is suppose to be elementary! Two categories, three calcs en Bob's your uncle...
When I look at the result set, the grouping looks fine (Company & Jobgrade). The issue is that the MIN, AVG & MAX values are exactly the same for each of the group by categories.
Here's the script:
SELECT
'[' + LTRIM(RTRIM(EMP_INFO_FIXED.CoNo)) + '] ' + LNK_COMPANY.CoDesc,
'[' + LTRIM(RTRIM(EMP_INFO_FIXED.JobgradeCode)) + '] ' + JobGradeLongDesc,
MIN(DISTINCT EarnFixed_01) AS [MinPackage],
AVG(DISTINCT EarnFixed_01) AS [AvgPackage],
MAX(DISTINCT EarnFixed_01) AS [MaxPackage]
FROM
EMP_INFO_FIXED INNER JOIN
EMP_EARNINGS ON
EMP_INFO_FIXED.PayPeriod = EMP_EARNINGS.PayPeriod AND
EMP_INFO_FIXED.CoNo = EMP_EARNINGS.CoNo LEFT JOIN
LNK_COMPANY ON
EMP_INFO_FIXED.CoNo = LNK_COMPANY.CoNo LEFT JOIN
LNK_JOBGRADE ON
EMP_INFO_FIXED.JobGradeCode = LNK_JOBGRADE.JobGradeCode
WHERE
EMP_INFO_FIXED.PayPeriod = '2007-12' AND
EMP_EARNINGS.EarnFixed_01 <> 0.00
GROUP BY
'[' + LTRIM(RTRIM(EMP_INFO_FIXED.CoNo)) + '] ' + LNK_COMPANY.CoDesc,
'[' + LTRIM(RTRIM(EMP_INFO_FIXED.JobgradeCode)) + '] ' + JobGradeLongDesc
ORDER BY
'[' + LTRIM(RTRIM(EMP_INFO_FIXED.CoNo)) + '] ' + LNK_COMPANY.CoDesc,
'[' + LTRIM(RTRIM(EMP_INFO_FIXED.JobgradeCode)) + '] ' + JobGradeLongDesc
I think I'm just overlooking one of those... right in your face... type of mistakes. Any body out there who's able to help?
Any assistance will be appreciated.
Regards,
S
April 27, 2008 at 9:04 am
are you sure that the columns being aggregated actually have different values in them? if the values are all the same, then the min/max/avg would also all be the same.
also, there's no point to the "distinct" in min(distinct colname) or max(distinct colname). you can leave off the distinct.
also, your avg is not a real avg if you are only averaging distinct values. probably you don't want a distinct in there either.
that is, (10,20) avg to 15. but these numbers also avg to 15 if you use distinct: (10,10,10,10,10,10,10,20). is that really your intent?
---------------------------------------
elsasoft.org
April 27, 2008 at 3:35 pm
Hi,
I get the same result without using DISTINCT. Each employees salary differs, therefore for each GROUP BY category the MIN, AVG, MAX should vary.
Here's a snippet of the result set:
[001] Health[77] Learner2668.7520078.94693191260.8
[001] Health[88] Trustee2668.7520078.94693191260.8
[001] Health[99] Non-Executive Director2668.7520078.94693191260.8
[002] LifeNULL500026790.58866189340.59
[002] Life[01] Executive Director500026790.58866189340.59
[002] Life[02] General Manager500026790.58866189340.59
[002] Life[03] Deputy General Manager500026790.58866189340.59
Sorry about the skewed columns.
Regards,
S
April 27, 2008 at 5:27 pm
It looks like data issue.
Try to run
SELECT
'[' + LTRIM(RTRIM(EMP_INFO_FIXED.CoNo)) + '] ' + LNK_COMPANY.CoDesc,
'[' + LTRIM(RTRIM(EMP_INFO_FIXED.JobgradeCode)) + '] ' + JobGradeLongDesc,
EarnFixed_01
FROM
EMP_INFO_FIXED INNER JOIN
EMP_EARNINGS ON
EMP_INFO_FIXED.PayPeriod = EMP_EARNINGS.PayPeriod AND
EMP_INFO_FIXED.CoNo = EMP_EARNINGS.CoNo LEFT JOIN
LNK_COMPANY ON
EMP_INFO_FIXED.CoNo = LNK_COMPANY.CoNo LEFT JOIN
LNK_JOBGRADE ON
EMP_INFO_FIXED.JobGradeCode = LNK_JOBGRADE.JobGradeCode
WHERE
EMP_INFO_FIXED.PayPeriod = '2007-12' AND
EMP_EARNINGS.EarnFixed_01 <> 0.00
ORDER BY '[' + LTRIM(RTRIM(EMP_INFO_FIXED.CoNo)) + '] ' + LNK_COMPANY.CoDesc,
'[' + LTRIM(RTRIM(EMP_INFO_FIXED.JobgradeCode)) + '] ' + JobGradeLongDesc,
and see what data sets you are actually aggregate.
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply