Apache OpenOffice Community Forum

I have a simple query that I want to format to 2 decimal places. Here is the query.

SELECT AVG( "Score" ) AS "Score", "Oil Pattern" FROM "Scores" GROUP BY "Oil Pattern" ORDER BY "Score" DESC

The database is simple. It's just 2 fields. An oil pattern and a score for that oil pattern. There are 12 oil patterns and several hundred scores for each one. Right now, the query comes out with the results something like this...

Viper - 261
Weber - 257
Dragon - 254

And so on.

I want the results to show with decimals like this...

Viper - 261.48
Weber - 257.29
Dragon - 254.73

How do I do this? I know I need something like...

AS Decimal (5,2) Varchar(5)

But no matter how I try to insert that in the above query I keep getting syntax errors. I'm thinking I'm missing a parenthesis somewhere but I'm not sure. Any assistance you experts can give me will be greatly appreciated.

Edit: Changed subject, was Need Help Formatting A Simple Query  Make your post understandable by others  -- MrProgrammer, forum moderator

Thanks.

Try casting your Score value as a DOUBLE before taking the average

FJCC wrote: ↑Fri Dec 29, 2023 5:29 pm Try casting your Score value as a DOUBLE before taking the average

Code: Select all

SELECT AVG(CAST("Score" AS DOUBLE)) AS "Score", "Oil Pattern" FROM "Scores" GROUP BY "Oil Pattern" ORDER BY "Score" DESC

Thank you. That did it. Now I'm going to study the code to see if I understand it. Much appreciated. Will mark as solved.

How do I mark as solved? Not letting me change subject.

By the way, it would be preferable to define the column as type DOUBLE in the table so you don't have to deal with CASTing in the query.

FJCC wrote: ↑Fri Dec 29, 2023 6:22 pm By the way, it would be preferable to define the column as type DOUBLE in the table so you don't have to deal with CASTing in the query.

Thank you. I didn't know that. I'm pretty new to this as you can probably guess. Still learning.