#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    2
    Rep Power
    0

    How to avoid double count?


    I have a query as follow:

    Select A.StudentName, A.TotalEnrolled, B.CourseName, B.Location from Students A inner join StudentCourses B on A.StudentId = B.StudentId

    and here is the sample result:

    StudentName | TotalEnrolled | CourseName | Location
    Student A | 2 | Math | 310
    Student A | 2 | Science | 210

    If I count TotalEnrolled from this result I will get 4, Is there anyway to create a query that returns TotalEnrolled only once for each student. Here is the result that I want:

    StudentName | TotalEnrolled | CourseName | Location
    Student A | 2 | Math | 310
    Student A | 0 | Science | 210

    Thanks for your help.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,382
    Rep Power
    391
    Code:
    Select A.StudentName, 
           case when row_number() over(partition by A.StudentName 
                                    order by B.CourseName) = 1 then
                A.TotalEnrolled else 0 end as totalEnrolled,
           B.CourseName, 
           B.Location 
      from Students A 
      join StudentCourses B 
        on A.StudentId = B.StudentId
     order
        by A.StudentName,
           B.CourseName
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2012
    Posts
    2
    Rep Power
    0
    swampBoogie,
    Thanks a lot for the solution.

IMN logo majestic logo threadwatch logo seochat tools logo