Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have to make query from DB. I found some solution but it isn't so precise as it need to be.

Here result of my query:

country     weight
Germany     1687.5
Gt.Britain  1687.5
Japan       1886.6666666666667
USA         1897.7777777777778

And correct query:

country     weight
Germany     1687.50
Gt.Britain  1687.50
Japan       1886.67
USA         1897.78

How to achieve this outcome?

Here is DB schema:

  • 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.

And my query looks next:

SELECT DISTINCT Classes.country, ( SELECT AVG( pen.p ) FROM
   (
    SELECT (c1.bore*c1.bore*c1.bore)/2 AS p FROM Classes AS c1, Ships AS s1
         WHERE c1.class=s1.class AND c1.country = Classes.country
            AND c1.bore IS NOT NULL
    UNION ALL
    SELECT (c2.bore*c2.bore*c2.bore)/2 FROM Classes AS c2, Outcomes
        WHERE c2.country = Classes.country AND c2.class=Outcomes.ship
            AND c2.bore IS NOT NULL
            AND Outcomes.ship NOT IN ( SELECT ss.name FROM Ships AS ss )
    ) AS pen
    WHERE pen.p IS NOT NULL
             ) AS weight
FROM Classes
WHERE Classes.country IS NOT NULL
  • How to count result set?
share|improve this question

closed as off-topic by Paul White, Mark Storey-Smith, RolandoMySQLDBA, ypercube, Michael - sqlbot Jan 12 at 21:50

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Paul White, Mark Storey-Smith, RolandoMySQLDBA, ypercube, Michael - sqlbot
If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer 1

up vote 4 down vote accepted

You can using the ROUND() function to specify the precision of your count.

share|improve this answer
    
can you give some hint how to use it at query? –  nazar_art Jan 11 at 16:13
2  
The link to the MySQL reference has hints on the various uses. –  Kevin Jan 11 at 16:14
2  
...or format the results with the right number of decimal places on the client. –  Aaron Bertrand Jan 11 at 19:08

Not the answer you're looking for? Browse other questions tagged or ask your own question.