Dividing count(*) by DateDiff always equals 1

Question

Post reply

Dividing count(*) by DateDiff always equals 1

thomas.lemesurier

Right there with Babe

Points: 719
More actions
June 12, 2008 at 5:37 am

#183427

Hi,
I have a log table, where I record information about items scanned on shelves. I'm now querying the table to see how long each shelf takes to scan, how many items on each shelf and finally, for each shelf, how many items were scanned in a second:
SELECT
DISTINCT shelf,
DATEDIFF(s, MIN(scan_datetime), MAX(scan_datetime)) AS timeelapsed,
count(*) as ItemCount,
count(*)/(DATEDIFF(s, MIN(scan_datetime), MAX(scan_datetime))) as ItemsPerSecond
FROM
scan_log
GROUP BY location
ORDER BY timeelapsed
Everything looks good apart from the ItemsPerSecond figure which always comes out as 1. If I switch the division round (to get seconds per item) it always comes out as 0. The TimeElapsed and Count values are both integers, so presumably that's why the result is only coming out as in integer too? How can I fix this? Thanks,
Tom

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Because both operands are int, you're getting integer division and the result is an int.

To avoid that, cast one of the operands to float or an appropriatly sized numeric. It doesn't matter which one.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 2

Have you tried converting your int's to decimals?

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

thomas.lemesurier Right there with Babe Points: 719 More actions · Answer 3

Thanks to both of you. Casting the count to a float did the trick,

Tom

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Here's a "cheater" method...

count(*)*1.0/(DATEDIFF(s, MIN(scan_datetime), MAX(scan_datetime))) as ItemsPerSecond

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

thomas.lemesurier Right there with Babe Points: 719 More actions · Answer 5

Interesting.

Using the count(*) * 1.0 method: 24/16 = 1.500000000000

Using the cast as float method: 24/16 = 1.5