Total # of Items grouped by rows

  • OK, here goes... I have a database that tracks ALL training and associated hours credited for each training session for our 1150 employees.

    We are required by our auditor to produce a year end report that shows what training courses each employee attended, along with their credited hours for the year. They have a required 24 hours of training each year.

    What I need is a report that compiles the data in a printed report that shows each person, the training courses they attended and the hours for each course. I then need it to total the hours at the end of each employee name before moving to the next name in the list.

    EXAMPLE Table:

    NAME Course # Course Name Hours

    Smith, BillAD1567 Animal Dental Care 8

    Smith, BillALG9989 Alligator Farming 16

    Smith, BillCGR9876 Cat Grooming 8

    Total Hours 32

    Jones, AliasPET3456 Pet Care, Basic 16

    Jones, AliasPETNU43 Pet Nutrition 40

    Total Hours 56

    Now, I can get the total hours using the SELECT SUM(Hours) AS Total Hours, Using the GROUP BY LAST NAME, however, this only returns a single entry for each persons name with their total hours displayed.

    Example of current return:

    NAME Division Section Total Hours

    Smith, Bill Intake Eval 32

    Jones, Alias Rehab Med 56

    I need a complete list of their training with the total hours. However, when I plug in the column name that contains the course name - I get the individual rows, the hours credited for that session, but nothing is totaled in the SELECT SUM(HOURS) AS TOTAL HOURS column.

    Here is my current SQL Statement that retuns every name in the dbase, with a single row and total hours...

    SELECT SUM([Course Hours]) AS Hours, Last, First, MI, Serial, Division, Section, Jurisdiction

    FROM tblTrainingRecords

    GROUP BY Last, First, MI, Serial, Division, Section, Jurisdiction

    ORDER BY Last

    Not good enough for the auditor - I need a report that shows ALL training courses and the total hours.....

    Any suggestions?

  • You can try WITH ROLLUP option with GROUP BY. But it will give you one extra row for each employee having the total hours.

    -Vikas Bindra

  • Interesting...

    Not concerned about an extra row as long as it's blank or could be used as a separator between name groups..

    Could you provide an example of how it would be used in my SQL statement?

    SELECT SUM([Course Hours]) AS Hours, Last, First, MI, Serial, Division, Section, Jurisdiction

    FROM tblTrainingRecords

    GROUP BY Last, First, MI, Serial, Division, Section, Jurisdiction

    ORDER BY Last

    In addition, when I try to include the course names, It seems to negate the SUM function and lists all training hours in individual rows assoicated with each entry. Does this make sense? Will the WITH ROLLUP function resolve this?

    Thank you for the information so far, it's been helpful

    Tom

  • here's one way to do it; in this case, i put the total Hours a s acolumn in the same row...not normalized, repetative, but the data is there.

    this is based of your original structure...if i had the actual table definitions and sample data, i would have refined it better:

    CREATE TABLE #EXAMPLE(

    NAME varchar(30),

    Course# varchar(30),

    CourseName varchar(30),

    Hours decimal(6,2) )

    INSERT INTO #EXAMPLE

    SELECT 'Smith, Bill','AD1567','Animal Dental Care', 8 UNION ALL

    SELECT 'Smith, Bill','ALG9989','Alligator Farming', 16 UNION ALL

    SELECT 'Smith, Bill','CGR9876','Cat Grooming', 8 UNION ALL

    SELECT 'Jones, Alias','PET3456','Pet Care, Basic', 16 UNION ALL

    SELECT 'Jones, Alias','PETNU43','Pet Nutrition', 40

    select

    #EXAMPLE.*,

    MyAlias.TotalHours

    from #EXAMPLE

    left outer join (select [NAME],SUM(Hours) as TotalHours from #Example Group By [Name]) MyAlias

    On #EXAMPLE.NAME = MyAlias.Name

    --Results:

    /*

    NAME Course# CourseName Hours TotalHours

    ------------- -------- ------------------- ------ -----------

    Smith, Bill AD1567 Animal Dental Care 8.00 32.00

    Smith, Bill ALG9989 Alligator Farming 16.00 32.00

    Smith, Bill CGR9876 Cat Grooming 8.00 32.00

    Jones, Alias PET3456 Pet Care, Basic 16.00 56.00

    Jones, Alias PETNU43 Pet Nutrition 40.00 56.00

    */

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thank you,

    That seems to simplify my process quite a bit.

    I'll give that a run on my tables and see what happens!

    Tom

  • Based on the example above, you could explore some new features such as multiple row inserts and CTEs with OVER and PARTITION BY, to accomplish the same thing. Obviously the row_number() function isn't providing any added value here, but it's fun to use, just because it's so easy. 🙂 At any rate, it could look like this:

    [font="Courier New"]DECLARE @Employees TABLE (

    EmployeeIDINT NOT NULL,

    EmployeeNamevarchar(30),

    CourseNumber varchar(30),

    CourseNamevarchar(30),

    CourseHours decimal(6,2)

    )

    INSERT INTO @Employees (EmployeeID, EmployeeName, CourseNumber, CourseName, CourseHours)

    VALUES(1, 'Smith, Bill','AD1567','Animal Dental Care', 8 )

    , (1, 'Smith, Bill','ALG9989','Alligator Farming', 16)

    , (1, 'Smith, Bill','CGR9876','Cat Grooming', 8)

    , (2, 'Jones, Alias','PET3456','Pet Care, Basic', 16)

    , (2, 'Jones, Alias','PETNU43','Pet Nutrition', 40)

    ;

    WITH cte AS

    (

    SELECT [EmployeeID]

    , SUM(CourseHours) AS TotalHours

    , row_number() OVER (PARTITION BY [EmployeeID]

    ORDER BY [EmployeeID] ASC) AS rownum

    FROM @Employees

    GROUP BY [EmployeeID]

    )

    SELECT e.*, t.TotalHours

    FROM cte t

    JOIN @Employees e

    ON e.[EmployeeID] = t.[EmployeeID]

    ORDER BY 1

    /*

    EmployeeIDEmployeeNameCourseNumberCourseNameCourseHoursTotalHours

    1Smith, BillAD1567Animal Dental Care8.0032.00

    1Smith, BillALG9989Alligator Farming16.0032.00

    1Smith, BillCGR9876Cat Grooming8.0032.00

    2Jones, AliasPET3456Pet Care, Basic16.0056.00

    2Jones, AliasPETNU43Pet Nutrition40.0056.00

    */[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply