question on count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

question on count

Author

Topic

helpme
Posting Yak Master

141 Posts

Posted - 2005-01-26 : 11:33:59

I have a question. I'm trying to run a statement similar to the following:

SELECT (COUNT(*)/2) FROM TBL1

The result is an even number, even if the total number of
records in the table is not an even number (result of 11 divided by 2 should be 5.5, not 5). If the total number of records
is an odd number, I want the result from the above select to include the '.5'. What am I doing wrong?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-26 : 11:36:48

SELECT COUNT(*), convert(decimal(12,2), COUNT(*)/2) FROM TBL1

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-26 : 11:38:21

your denomimator would need to be cast as some kind of numeric or float datatype rather than integer
ie:
SElect (count(*)/ convert(numeric(15,2),2) ) From Tbl1

helpme
Posting Yak Master

141 Posts

Posted - 2005-01-26 : 11:38:30

Thanks for the reply

helpme
Posting Yak Master

141 Posts

Posted - 2005-01-26 : 13:03:03

If I may impose one more time, if I wanted to take this one step further and round up (11 divided by 2 = 5.5, round up to 6), how would I do that?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-26 : 13:08:13

SELECT (COUNT(*)/2)+1 FROM TBL1
or
SElect round((count(*)/ convert(numeric(15,2),2)), 0) From TBL1

Go with the flow & have fun! Else fight the flow

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-26 : 13:50:43

quote:
SELECT COUNT(*), convert(decimal(12,2), COUNT(*)/2) FROM TBL1

I'm a minimalist...

SELECT COUNT(*) / 2.0 FROM TBL1

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-26 : 14:06:34

quote:
Originally posted by SamC

[quote]I'm a minimalist...

What? You got religon?

Brett

8-)

lici
Starting Member

11 Posts

Posted - 2005-01-26 : 14:58:54

Select count(*)/2.0

SamC
White Water Yakist

3467 Posts

Posted - 2005-01-26 : 15:23:25

quote:
Originally posted by X002548

quote:
Originally posted by SamC

[quote]I'm a minimalist...

What? You got religon?

Yes... the church of Holy Smoke!

More is not always more... sometimes less is more... or more is less... more or less... that is.

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-26 : 15:55:10

If this is another Bud Light commercial....I mean...why not just drink water....

Brett

8-)

helpme
Posting Yak Master

141 Posts

Posted - 2005-01-26 : 16:58:49

Thanks everyone

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-01-26 : 19:43:12

quote:
Originally posted by SamC

quote:
SELECT COUNT(*), convert(decimal(12,2), COUNT(*)/2) FROM TBL1

I'm a minimalist...

SELECT COUNT(*) / 2.0 FROM TBL1

If I were an accountant...
SELECT COUNT(*) FROM (SELECT TOP 50 PERCENT * FROM TBL1) T

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources