How can I make QA say 1/2=.5 instead of 1/2=0?

[NSMan]

I know this sounds like a dumb question, and I am currently looking through the SQL Books Online, but I figured somebody out there might have a quick answer for me...

 

[SQLDenis]

Just add * (1.0)

select 1 * (1.0)/2

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[NSMan]

Maybe I oversimplified my question. I'm actualy trying to do a count, divide the result into 100, do another count based on groups, and figure out what percentage of the whole count a group consists of. A simplified version would look something like this...

select count(id)/(select count(id) from completed sales\100)
from
completedsales
group by item

If I could just get it to use decimals when it divides instead of integers, I think my problem would be solved...

 

[SQLDenis]

select count(id)/(select count(id) from completed sales\100.00)
from
completedsales
group by item

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[NSMan]

*Beats head against desk repeatedly*

Thank you very much!

 

[SQLDenis]

No problem

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Typo aside (\ vs /), I'm curious how it works...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

I think a implicit conversions to numeric/decimal occurs

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

And it's not money data type because you can do this

select 2580 /100.000035665665

with money the answer would be different

declare @m money
select @m = 100.000035665665
select 2580/@m

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

OK that , but that syntax should be incorrect... even if you replace \ with / (my guess). Plus one table instance is named "completed sales" and another one "completedsales".

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

I didn't even look at the complete statement I just replaced 100 with 100.00

Probably pseudocode by op ;-)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[r937]

yeah, obviously this expression --

(select count(id) from completed sales/100.00)

has serious syntax problems

too much rum in the eggnog, SQLDenis? ;-)





r937.com | rudy.ca

 

[vongrunt]

Yup, GIGO.

-- syntax error
select count(id)/(select count(id) from completedsales/100.0)
from completedsales
group by item

-- works
select count(id)/((select count(id) from completedsales)/100.0)
from completedsales
group by item

-- works
select count(id)/(select count(id)/100.0 from completedsales)
from completedsales
group by item

-- does not work
select count(id)/(select count(id) from completedsales)*100.0
from completedsales
group by item

-- works, the best choice IMHO (highest precedence data type is the leftmost one)
select 100.0*count(id)/(select count(id) from completedsales)
from completedsales
group by item

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[NSMan]

Well, that isn't the exact syntax I'm working with a datastructure that doesn't make a whole lot of sense to somebody who has never deal with it. Basicly it is one hudge flat file called object, with 50 different fields which allows the table to store completly different data types into one table. (A typ 4 record might be sales, and typ 5 record my be customers, ect) So that was something created on the fly. My real select statement looks something like this...

select
a2,
left((count(id)/(select count(id)/100.00 from object where typ=4 and link2=1029289 and (len(a18)>3 or len(a19)>3))),5)

from object where
typ=4 and
(len(a18)>3 or len(a19)>3) and
link2=1029289
group by a2

 

[r937]

oh. my. god.

r937.com | rudy.ca

 

[SQLDenis]

r937

I have to build up my tolerance for new years eve and have been doing a loading phase with rum (instead of creatine)
I am getting there, by new years eve I will be able to drink 6 barrels off eggnog

Like i said before I just replaced 100 with 100.00, I was sure that was enough for NSMan to make it work

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/