...

View Full Version : sum 2 columns?



nikko50
04-10-2011, 09:02 PM
I'm tryig to get the sum of total time spent on each activity worked by employees. Below is the sql I have but it does not sum. I need to sum hours and minutes to get a total amount of time spent on each ACTIVITY for a date range? How can I do this?



SELECT *,SUM(HOURS),SUM(MINUTES)
FROM DAILY_ACTIVITY
WHERE ACTIVITY_DATE >= '2011-03-03'
AND ACTIVITY_DATE <= '2011-04-11'
AND HOURS + MINUTES > 0
AND USERID = '36'
GROUP BY ACTIVITY

Old Pedant
04-11-2011, 03:56 AM
Your GROUP BY needs to specify *ALL THE FIELDS* that you want to group on.

In general, that should be all the fields in your SELECT statement (other than the ones in the aggregate functions...SUM and COUNT and MAX and MIN, etc., are all aggregate functions).

So:


SELECT ACTIVITY,SUM(HOURS),SUM(MINUTES)
FROM DAILY_ACTIVITY
WHERE ACTIVITY_DATE >= '2011-03-03'
AND ACTIVITY_DATE <= '2011-04-11'
AND HOURS + MINUTES > 0
AND USERID = '36'
GROUP BY ACTIVITY

In general, you should never use SELECT * when working with GROUP BY. It's almost impossible to get the results you need/expect if you do so.

I'm not sure what the purpose of AND HOURS + MINUTES > 0 is. If their values are zero, so what? Then they won't add anything to the SUM() results.

Finally, that query is going to get you something like

breathing 196 1221
eating 13 493

That is, 196 hours and 1221 minutes, etc.

Wouldn't it be more reasonable to get hours and minutes as an OVERALL total?

So:


SELECT ACTIVITY, SUM(HOURS * 60 + MINUTES)/60.0 AS totalHours
FROM DAILY_ACTIVITY
WHERE ACTIVITY_DATE >= '2011-03-03'
AND ACTIVITY_DATE <= '2011-04-11'
AND HOURS + MINUTES > 0
AND USERID = '36'
GROUP BY ACTIVITY

And so get something like


breathing 216.35
eating 21.17

???



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum