Cast&Round problem - 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 2008 Forums

Transact-SQL (2008)

Cast&Round problem

Author

Topic

idkase
Starting Member

6 Posts

Posted - 2014-04-03 : 06:00:03

Database:

The database of naval ships that took part in World War II is under consideration. The database has the following relations:
Classes(class, type, country, numGuns, bore, displacement)
Ships(name, class, launched)
Battles(name, date)
Outcomes(ship, battle, result)
Ships in classes are arranged to a single project. A class is normally assigned the name of the first ship in the class under consideration (head ship); otherwise, the class name does not coincide with any ship name in the database.
The Classes relation includes the class name, type (bb for a battle ship, or bc for a battle cruiser), country where the ship was built, number of main guns, gun caliber (diameter of the gun barrel, in inches), and displacement (weight in tons). The Ships relation includes the ship name, its class name, and launch year. The Battles relation covers the name and date of a battle the ships participated; while the result of their participation in the battle (sunk, damaged, or unharmed - OK) is in the Outcomes relation.
Notes: 1) Outcomes relation may include the ships not included in the Ships relation. 2) Sunk ship can not participate in battles after that.

Exercise

To within two decimal digits, define the average amount of guns for the battleship classes.

What I've got so far:

SELECT ROUND(MEDIA, 2) AS MEDIAR FROM (SELECT (SUMA/CUENTA) AS MEDIA FROM (SELECT CAST(SUM(NUMGUNS) AS NUMERIC(4,2)) AS SUMA, CAST(COUNT(NUMGUNS) AS NUMERIC(3,2)) AS CUENTA FROM CLASSES where type = 'bb') A) B

My query:

MEDIAR
9.670000

Correct query

Avg-numGuns
9.67

I'm trying to remove those 0000 in my querys, But I can't find the way to do it. I tried a cast to numeric(3.2) but an error arises.

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-04-03 : 06:33:44

[code]
SELECT ROUND(MEDIA, 2) AS MEDIAR FROM
[/code]

try to replace with
[code]
SELECT CAST(ROUND(MEDIA, 2) AS DECIMAL(18,2)) AS MEDIAR FROM
[/code]

sabinWeb MCP

idkase
Starting Member

6 Posts

Posted - 2014-04-03 : 10:07:36

It didnt work

Error :

An error arises on second database. Error code (ODBC) - 22003

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-04-03 : 15:35:46

Idkase, are you using SQL Server?
If yes, if your version is above 2005, use the below query..


;WITH CTE AS
(
SELECT CAST(SUM(NUMGUNS) AS numeric(4,2)) AS SUMA, CAST(COUNT(NUMGUNS) AS numeric(3,2)) AS CUENTA
FROM Classes WHERE [Type] = 'bb')
)
SELECT CONVERT(DECIMAL(10,2), (SUMA/CUENTA))
FROM CTE

--SELECT CONVERT(DECIMAL(10,2), 9.6700)

Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-04-03 : 16:22:43

SELECT CAST(MEDIA AS NUMERIC(5, 2)) AS AvgNumGuns FROM (SELECT (SUMA/CUENTA) AS MEDIA FROM (SELECT CAST(SUM(NUMGUNS) AS NUMERIC(4,2)) AS SUMA, CAST(COUNT(NUMGUNS) AS NUMERIC(3,2)) AS CUENTA FROM CLASSES where type = 'bb') A) B

idkase
Starting Member

6 Posts

Posted - 2014-04-04 : 03:09:15

I'm not using sql-server, I'm just training at sql-ex.ru, which uses MySql.

For ScottPletcher, that is not working either, I'm receiving the same error.

idkase
Starting Member

6 Posts

Posted - 2014-04-04 : 08:40:04

Just so you know, I solved it this way:

select Substring(cast(AVG as Varchar), 1, LEN(avg)-4) from (select Round(AVG(num), 2) as AVG from (select cast(numGuns as Numeric (4,2)) as num from (select numGuns from classes where type = 'bb') a) b) c

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-04-04 : 09:52:50

No wonder since this is a SQL Server 2008 forum.

djj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources