Problem understanding SUM function with GROUP BY

Question

Post reply

Problem understanding SUM function with GROUP BY

DaveBriCam

SSCarpal Tunnel

Points: 4298

December 6, 2021 at 3:06 pm

Go to Answer

#3960874

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

Phil Parkin

SSC Guru

Points: 246960
More actions
December 6, 2021 at 3:20 pm
Answer
#3960880
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

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

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

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 1

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 (?)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 3

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 2 years, 11 months ago by DaveBriCam.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

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?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 5

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