Using Sum and Cast Together

Question

Using Sum and Cast Together

doug 40899

SSCommitted

Points: 1656
More actions
November 4, 2010 at 1:24 pm

#97525

I'm trying to sum a column as well as round a column up to the nearest minute. Here's 2 queries I'm running, but I'd like to combine them in to one. Can anyone offer any suggestions:
Query 1
select name, employeenumber, sum (ontime) as totalminutes into scratchpad2
from scratchpad1
where date between '5/1/2010' and '5/15/2010'
group by employeenumber, name
order by employeenumber asc
Query 2
select totalminutes, (cast (totalminutes as decimal (10,2))) as realtime
from scratchpad1
Thank you
Doug

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

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

There is a Round() function. You should be able to wrap that around the Sum() function in your first query. Have you tried that?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

doug 40899 SSCommitted Points: 1656 More actions · Answer 2

Gsqaured,

I dont think that the round function would work unless it's after I do the cast, because it is in a decimal format before that. Can you show me an example of what you mean though?

Thanks

Doug

GSquared SSC Guru Points: 260824 More actions · Answer 3

I guess I don't understand what you're asking.

What type of data does your "ontime" column contain? What does the sum of it look like?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

doug 40899 SSCommitted Points: 1656 More actions · Answer 4

G

The ontime column contains data that looks like this:

15.01015

and what I need to do is to round it to the nearest minute. I have to use a conversion (([LoggedIn]/1000/60) to get this number ... but thats in an earlier query.

Does that help?

GSquared SSC Guru Points: 260824 More actions · Answer 5

And what does it contain? The number of seconds they were online? Minutes and fractions thereof?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

doug 40899 SSCommitted Points: 1656 More actions · Answer 6

Yes, its recorded in 15 minute increments. It's minutes and seconds.

GSquared SSC Guru Points: 260824 More actions · Answer 7

So if the sum ends up at 15.01015, and you want to round to the nearest minute, how does "round(15.01015, 0)" not get you what you need? It'll return "15". It'll return 16 for 15.5001, and so on.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon