• KoldCoffee (5/1/2013)


    Chris,

    thanks for reply. I should have posted what the result set was that I'm after. There should be two rows for each person, one recording total count of tickets and the other total duration of tickets. It should look like this:

    Name, Category, Value

    Helen | Count | 2

    Helen |Duration| 28

    Joe | Count | 2

    Joe |Duration| 33

    Blake | Count | 2

    Blake |Duration| 62.

    If Helen had another ticket, then count would go up to 3 and Duration would increase.

    ;with ct1 as (

    select

    name

    ,Value = COUNT(ticketID)

    , Category = 'Count'

    from #temp

    group by Name

    ),

    ct2 AS (

    select

    name

    , Value= (Select sum(Duration))

    , Category = 'Duration'

    from #temp

    group by name

    )

    SELECT *

    FROM ct1

    UNION ALL

    SELECT *

    FROM ct2

    ORDER BY Name

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden