query with inline views and rounding

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

SQL Server Development (2000)

query with inline views and rounding

Author

Topic

erik.wierenga
Starting Member

3 Posts

Posted - 2005-04-15 : 08:16:28

I got this query working for oracle, but haven't got a clue how to translate it into sql server.

select round(c1.a/c2.b*100,2) from
(select count(*) a from r5events where evt_rtype = 'JOB') c1,
(select count(*) b from r5events where evt_rtype in ('PPM','JOB')) c2

If anyone could help me with this one.

Erik

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-04-15 : 08:22:39

?? Just run it, assuming the tables are there.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

erik.wierenga
Starting Member

3 Posts

Posted - 2005-04-15 : 08:26:14

I tried that before, but the result is 0. All the tables are there and both select statements give normal results when runned seprately. It think it must be something around the round function.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-04-15 : 08:35:52

Give us a CREATE TABLE, INSERT data, run you SELECT example. We can then get you a solution fast. The format is fine. You might want to use paranthesis around your division and multiplication so you know exactly what it's doing, and it's clear to people who don't know what order it would execute in.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-15 : 11:27:21

The datatype of a count(*) is integer, so you are doing integer math:
1/3 = 0
5/4 = 1
etc.

Try this to force the datatypes to numeric:


select
	round((c1.a*1.00000)/(c2.b*100.00000),2)
from 
	(select count(*) a from r5events where evt_rtype = 'JOB') c1, 
	(select count(*) b from r5events where evt_rtype in ('PPM','JOB')) c2

You should also read about this whole subject in SQL Server Books Online.

Also, the term in SQL Server is not "inline views". The documentation calls them derived tables.

CODO ERGO SUM

erik.wierenga
Starting Member

3 Posts

Posted - 2005-04-18 : 04:25:21

Thank you, this query works.

I'm still trying to find a good course for SQL-server somewhere in the neighbourhood.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources