Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unhappy Unanswered: sum function yielding bad results

    I've inherited the following code:

    SELECT TOP 100 PERCENT dbo._Options_Demographics.GradeLevel, dbo._Options_Demographics.Gender, dbo._Options_Demographics.Ethnicity,
    dbo._Options_Demographics.Age, SUM(dbo._Options_Demographics.Enrollment) AS Enrollment, dbo._Options_Demographics.OptionsYear,
    dbo._Options_Demographics.OptionsMonth
    FROM dbo._Options_Confirmed INNER JOIN
    dbo._Options_Demographics ON dbo._Options_Demographics.DistrictCode = dbo._Options_Confirmed.DistrictCode
    GROUP BY dbo._Options_Demographics.GradeLevel, dbo._Options_Demographics.Gender, dbo._Options_Demographics.Ethnicity,
    dbo._Options_Demographics.Age, dbo._Options_Demographics.OptionsYear, dbo._Options_Demographics.OptionsMonth
    ORDER BY dbo._Options_Demographics.OptionsYear, dbo._Options_Demographics.OptionsMonth, dbo._Options_Demographics.GradeLevel,
    dbo._Options_Demographics.Gender, dbo._Options_Demographics.Ethnicity, dbo._Options_Demographics.Age

    which returns this data (the enrollment value is 4 times the correct value)
    grade gender eth age enroll year month
    8 Female Black 16 1811 2003 December
    8 Female Black 17 1621 2003 December
    8 Female Black 18 793 2003 December
    8 Female Black 18P 287 2003 December



    If I remove the sum function and manually add, my totals are correct ...
    SELECT TOP 100 PERCENT _Options_Demographics.GradeLevel, _Options_Demographics.Gender, _Options_Demographics.Ethnicity,
    _Options_Demographics.Age, _Options_Demographics.Enrollment, _Options_Demographics.OptionsYear,
    _Options_Demographics.OptionsMonth
    FROM _Options_Confirmed RIGHT OUTER JOIN
    _Options_Demographics ON _Options_Demographics.DistrictCode = _Options_Confirmed.DistrictCode
    GROUP BY _Options_Demographics.GradeLevel, _Options_Demographics.Gender, _Options_Demographics.Ethnicity, _Options_Demographics.Age,
    _Options_Demographics.Enrollment, _Options_Demographics.OptionsYear, _Options_Demographics.OptionsMonth
    ORDER BY _Options_Demographics.OptionsYear, _Options_Demographics.OptionsMonth, _Options_Demographics.GradeLevel,
    _Options_Demographics.Gender, _Options_Demographics.Ethnicity, _Options_Demographics.Age

    I've been looking at this for too long and I know that is something very trivial ...

    William

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The query you posted should give the total Enrollment for each district, assuming that GradeLevel, Gender, Ethnicity, Age, OptionsYear, and OptionsMonth represent a natural unique key on your outer table. If they do not, then your Enrollment total will be multiplied by the number of duplicates in the OptionsConfirmed table.
    (Why do your table names start with underscores?)

    This might give you what you want:

    SELECT distinct TOP 100 PERCENT
    dbo._Options_Demographics.GradeLevel,
    dbo._Options_Demographics.Gender,
    dbo._Options_Demographics.Ethnicity,
    dbo._Options_Demographics.Age,
    DisctrictEnrollment.Enrollment,
    dbo._Options_Demographics.OptionsYear,
    dbo._Options_Demographics.OptionsMonth
    FROM dbo._Options_Confirmed
    INNER JOIN
    (select dbo._Options_Demographics.DistrictCode,
    SUM(dbo._Options_Demographics.Enrollment) AS Enrollment
    From dbo._Options_Demographics
    group by dbo._Options_Demographics.DistrictCode) DistrictEnrollment
    ON DistrictEnrollment.DistrictCode = dbo._Options_Confirmed.DistrictCode
    ORDER BY dbo._Options_Demographics.OptionsYear,
    dbo._Options_Demographics.OptionsMonth,
    dbo._Options_Demographics.GradeLevel,
    dbo._Options_Demographics.Gender,
    dbo._Options_Demographics.Ethnicity,
    dbo._Options_Demographics.Age
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2004
    Posts
    2
    Thanks, that was the case in that they are not 'keys' ... I did inherit the 'underscores' also. I need to rework your code and when it works, I will let you know. Thanks a bunch.

    William

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •