Return percentages in the query.

[sqlservernewbie]

Hello,

I'm trying to something that just works in Oracle, but does not in Sql
Server.

How can I get the percentage of two counts to be returned on each row
of the query?

select count(sid), /* all not the not null */
count(*),
(count(sid) / count(*) ) as percent_not_null,
4 as four,
(3/4) as three_over_four
from dbo.sysusers
70 82 0 4 0
Incredibly, it changes even 3/4 into a zero!

For efficiency, I want the percentage returned in the query.
And to not use variables and coding. Efficiency,
both of the server, and of my time.
Note: I am using dbo.sysusers as an EXAMPLE only. My real
query will be on user defined application tables.
What is the solution please?

[SQL Menace]

Integer math

here are 3 ways pick your poison

select 3/4,
(3*1.0)/4,
3.0/4,
convert(decimal(12,3),3)/4

Denis the SQL Menace
http://sqlservercode.blogspot.com/
[e-mail] wrote:

Hello,

I'm trying to something that just works in Oracle, but does not in Sql
Server.

How can I get the percentage of two counts to be returned on each row
of the query?

select count(sid), /* all not the not null */
count(*),
(count(sid) / count(*) ) as percent_not_null,
4 as four,
(3/4) as three_over_four
from dbo.sysusers
70 82 0 4 0
Incredibly, it changes even 3/4 into a zero!

For efficiency, I want the percentage returned in the query.
And to not use variables and coding. Efficiency,
both of the server, and of my time.
Note: I am using dbo.sysusers as an EXAMPLE only. My real
query will be on user defined application tables.
What is the solution please?

[Aaron Bertrand [SQL Server MVP]]

Incredibly, it changes even 3/4 into a zero!

This is called integer math.
http://classicasp.aspfaq.com/general...oes-4/5-0.html

[--CELKO--]

>I'm trying to something that just works in Oracle, but does not in SQL Server. <<

Oracle is the one that does not work and SQL Server is the one that
follows Standards. Wait until you find out how Oracle also screws up
NULLs. If you want decimal placs, then you can CAST() the values to
your needed precision. If you use a number with a decimal point in
it, SQL Server will pick a default precision.

[Madhivanan]

Try

select count(sid), /* all not the not null */
count(*),
(count(sid)*1.0 / count(*) ) as percent_not_null,
4 as four,
(3/4) as three_over_four
from dbo.sysusers
Madhivanan
sq*************@yahoo.com wrote:

Hello,

I'm trying to something that just works in Oracle, but does not in Sql
Server.

How can I get the percentage of two counts to be returned on each row
of the query?

select count(sid), /* all not the not null */
count(*),
(count(sid) / count(*) ) as percent_not_null,
4 as four,
(3/4) as three_over_four
from dbo.sysusers
70 82 0 4 0
Incredibly, it changes even 3/4 into a zero!

For efficiency, I want the percentage returned in the query.
And to not use variables and coding. Efficiency,
both of the server, and of my time.
Note: I am using dbo.sysusers as an EXAMPLE only. My real
query will be on user defined application tables.
What is the solution please?