Solved

mySQL. SQL query. Substitute for Numeric key word.

Posted on 2016-11-02
3
47 Views
Last Modified: 2016-11-02
Let say I need to solve the problem :

With a precision of two decimal places, determine the average number of guns for the battleship classes.


SELECT AVG(numGuns+0.0)
FROM Classes
WHERE type = 'bb'

Open in new window


The query about work fine but show me result with more than 2 decimal points.

I need to write sql query that will be analog query below but will be work on mySQL server ( as far as I understand Numeric key word didn't work in mySQL server )
SELECT CAST ( AVG(numGuns+0.0) AS NUMERIC(10,2) )
FROM Classes
WHERE type = 'bb'

Open in new window


So how can I rewrite the query above in order that it'll work in mySQL server.
Thx in advance !
0
Comment
Question by:SunnyX
3 Comments
 
LVL 13

Assisted Solution

by:F Igor
F Igor earned 56 total points
ID: 41870557
You can use the FORMAT function


SELECT FORMAT( numeric_expression, decimals) FROM table

Open in new window



http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_format
0
 
LVL 24

Accepted Solution

by:
Pawan Kumar earned 444 total points
ID: 41870612
Try..

--WITH OUT ROUNDING

SELECT TRUNCATE( AVG(numGuns) ,  2 )  AS numGuns
FROM Classes
WHERE type = 'bb'

--WITH ROUNDING

SELECT ROUND( AVG(numGuns) ,  2 )  AS numGuns
FROM Classes
WHERE type = 'bb'
0
 

Author Closing Comment

by:SunnyX
ID: 41870769
thx everybody !
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now