CODEWITHSUNDEEP
sqldecimal
tryAndroid
tryAndroid

Reputation: 95

2 digits after decimal point pure SQL

This is my code right now:

SELECT 
j.job_title,
ROUND(CAST(SUM(j.salary) / COUNT(j.id)as float)) as average_salary,
COUNT(p.id) as total_people,
ROUND(CAST(SUM(j.salary)/2 as float)) as total_salary
FROM people p
JOIN job j on p.id=j.people_id
GROUP BY j.job_title
ORDER BY average_salary desc;

The result on average_salary shows no decimals. Without CAST, the result shows more than 5 decimals. How do I change my code to make it show only 2 digits after decimal point?

I have tried several other code like using decimal(16,2) or numeric(16,2) instead of float, but it does not seem to work.

I want this with pure SQL. Thanks

Upvotes: 2

Views: 2874

Answers (3)

Adigil87
Adigil87

Reputation: 1

Try this:

ROUND(SUM(j.salary) / COUNT(j.id), 2) as average_salary

If you want the result as float, try:

SELECT CAST(SUM(j.salary) / COUNT(j.id) AS NUMERIC(10,2)) as average_salary

or

SELECT CAST(round(SUM(j.salary) / COUNT(j.id),2) AS NUMERIC(10,2)) as average_salary

or

SELECT CAST(round(SUM(j.salary) / COUNT(j.id),2) AS DECIMAL(10,2)) as average_salary

Notice that the answer could be shown with no zero, in a case the second decimal digit is 0, depends on the database platform you use.

One of them should work.

Upvotes: 0

Igor Grava
Igor Grava

Reputation: 16

Try this:

SELECT 
j.job_title,
CONVERT(NUMERIC(15,2),  SUM(j.salary) / COUNT(j.id) ) as average_salary

Thanks

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Instead of ROUND() convert to DECIMAL(). Something like this:

SELECT j.job_title,
       CAST(AVG(j.salary) as DECIMAL(16, 2)) as average_salary,
       COUNT(p.id) as total_people,
       CAST(SUM(j.salary) / 2 as DECIMAL(16, 2)) as total_salary

If you want to use ROUND() then use it before the conversion to decimal, not after.

Upvotes: 3

Related Questions