CODEWITHSUNDEEP
sql-server
Andrew
Andrew

Reputation: 65

Breaking down data into groups

I have the code 

SELECT     TOP (100) PERCENT dbo.census.Group_Code, dbo.census.Gender, SUM(dbo.v_Courses.Dur_In_Hours) 
                      AS SumOf_Dur_In_Hours
FROM         dbo.v_Courses INNER JOIN
                      dbo.census ON dbo.v_Courses.Job_Group_Code = dbo.census.Group_Code
WHERE     (dbo.v_Courses.System = 'GEMS') AND (NOT (dbo.v_Courses.Course_ID LIKE 'UPS%')) AND 
                      (dbo.v_Courses.First_Access_date BETWEEN CONVERT(DATETIME, '2012-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2012-12-31 00:00:00', 
                      102))
GROUP BY dbo.census.Group_Code, dbo.census.Gender
ORDER BY dbo.census.Group_Code

Its purpose to give me the total hours of my job level and gender, but the job levels are all returning the same number of hours for each gender. And I cannot figure out how to get them to separate.

SO the results look liket this, but there is no way each gender has the same number of hours.

Job     Gen         Hours
11  F   10886.50417
11  M   10886.50417
16  F   33494.17222
16  M   33494.17222
16  U   33494.17222
21  F   19267.76361
21  M   19267.76361

It is the Gender that is causing the duplicates

Upvotes: 0

Views: 80

Answers (2)

Frederic
Frederic

Reputation: 1028

can you include gender on inner join? or v_courses dont have this field?

SELECT     TOP (100) PERCENT dbo.census.Group_Code, dbo.census.Gender, SUM(dbo.v_Courses.Dur_In_Hours) 
                  AS SumOf_Dur_In_Hours
FROM         dbo.v_Courses 
INNER JOIN dbo.census 
   ON dbo.v_Courses.Job_Group_Code = dbo.census.Group_Code
   AND dbo.v_Courses.Gender = dbo.census.Gender
WHERE     (dbo.v_Courses.System = 'GEMS') AND (NOT (dbo.v_Courses.Course_ID LIKE 'UPS%')) AND 
                  (dbo.v_Courses.First_Access_date BETWEEN CONVERT(DATETIME, '2012-01-01 00:00:00', 102) AND CONVERT(DATETIME, '2012-12-31 00:00:00', 
                  102))
GROUP BY dbo.census.Group_Code, dbo.census.Gender
ORDER BY dbo.census.Group_Code

Upvotes: 1

bendataclear
bendataclear

Reputation: 3850

If you run the query without the group do you see duplicate lines? If so you probably need another restriction in the join/where.

Upvotes: 1

Related Questions