sql server (alternate): Return percentages in the query. -- sql server (alternate)

Return percentages in the query.

sqlservernewbie NO[at]SPAM yahoo.com
10/13/2006 9:27:49 AM

sql server (alternate): 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?

Re: Return percentages in the query.

SQL Menace
10/13/2006 9:30:34 AM

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/

[quoted text, click to view]

Re: Return percentages in the query.

--CELKO--
10/13/2006 11:16:57 AM

[quoted text, click to view]

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.

Re: Return percentages in the query.

Aaron Bertrand [SQL Server MVP]
10/13/2006 12:31:52 PM

[quoted text, click to view]

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

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server (alternate) group.