Problem understanding SUM function with GROUP BY

  • I have code I'm trying to write to sum the time in decimal for each user_name:

    SELECT [user_name], dbo.fnGetDecimalTime([event_sec]) as 'Decimal_Time', 
    SUM(dbo.fnGetDecimalTime([event_sec])) as 'Decimal_Time_SUM'
    FROM [Charter].[dbo].[ConvoHrs]
    group by [user_name], [event_sec]
    order by [user_name], [event_sec] desc

    But my data comes back looking like this:

    Capture12

  • Try this

    SELECT [user_name], SUM(dbo.fnGetDecimalTime([event_sec])) as 'Decimal_Time_SUM'
    FROM [Charter].[dbo].[ConvoHrs]
    group by [user_name]
    order by [user_name]

    You cannot group by a column (Decimal_Time) and then hope that it is ignored when the results are displayed.

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • Thanks! One thing what if I need to add a field like [Date] (which I may want to do grouping on later)? It wants this added to the 'group by' and 'order by' and goes back to the original issue (?)

  • DaveBriCam wrote:

    Thanks! One thing what if I need to add a field like [Date] (which I may want to do grouping on later)? It wants this added to the 'group by' and 'order by' and goes back to the original issue (?)

    If you need another column, it can of course be added. But you must either

    a) Perform an aggregate function on it (SUM, MAX etc)

    b) GROUP BY it

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • This does not seem to work, the data returns like before:

    SELECT [user_name], [created_at],
    SUM(dbo.fnGetDecimalTime([event_sec])) as 'Decimal_Time_SUM'
    FROM [Charter].[dbo].[ConvoHrs]
    group by [user_name],[created_at]
    order by [user_name]

    • This reply was modified 1 month, 2 weeks ago by  DaveBriCam.
  • You really can't see why?

    If User X has 10 rows of data, each with a different 'created-at' value, which one of those values would you wish to return in the single aggregated row which is returned for that user?

    If you aren't happy single, you won't be happy in a relationship.

    Remember, happiness comes from guitars, not relationships.

  • Sorry... I see now and tried MAX and that took care of it... thanks!!

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

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