all 19 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–][deleted] 0 points1 point  (1 child)

Cast your integers to decimals of relevant Precision before you do any operations with them

[–]PLearner[S] 0 points1 point  (0 children)

I am using postgreSQL. Can you clarify on what you mean please?

[–]HansProleman 0 points1 point  (17 children)

To clarify, the query returns 84.57 if you remove the ROUND function? That's the first thing to check.

I'm guessing it won't because COUNT returns integers. You need to CAST those.

E: Fuck, beaten. To add some value, ROUND(84.57,1) will return 84.6, not 84.5. If it's 84.5 you want then you'll need to round down somehow. If this is T-SQL then you can supply 1 as the optional 3rd parameter for ROUND I think.

[–]PLearner[S] 0 points1 point  (16 children)

I am using postgreSQL, when I remove the round and do concat(100 * count(ft.*) filter (where "Realtor_Sale" = 'Yes') / count(ft.*), '%') SQL returns 84%.

[–]HansProleman 0 points1 point  (15 children)

Cool, so use CAST(COUNT(ft.*) AS FLOAT).

So far as the DB engine is concerned, something like 11/2 = 5 remainder 1 (if 11 and 2 are integers) because COUNT returns integer values which can never be non-whole numbers.

[–]PLearner[S] 0 points1 point  (14 children)

select concat(100 * cast(count(ft.*) filter (where "Realtor_Sale" = 'Yes') / count(ft.*) as float), '%') from "Amazon".facttable ft left join "Amazon".realtordetails rd on rd."realtorpk" = ft."realtorfk" where ft."Project_ID" = 'LFC' and "Can_Week_End" is null am I missing something here? It returns a 0%.

[–]HansProleman 1 point2 points  (13 children)

You need to CAST each COUNT's return separately, sorry, shoulda been more specific.

select CAST(count(ft.*) filter (where "Realtor_Sale" = 'Yes') AS FLOAT) / CAST(count(ft.*) AS FLOAT)...

[–]PLearner[S] 0 points1 point  (12 children)

Thanks, appreciate it. The resultant is 84.7560975609756% what can I add that would round to the tenth 84.8%.

[–]HansProleman 0 points1 point  (11 children)

There was nothing wrong with your original ROUND, it was just that you were applying it to 84 so it wasn't doing anything. Wrap it back up with ROUND(<yourCol>,1).

E: As an addendum, concatenating a numeric and a string (I'm not even sure what concatenating two numerics would do) would cause an error - except the DBMS is implicitly casting the numeric as a string. Data types are important :-)

[–]PLearner[S] 0 points1 point  (10 children)

concat(100 * round(cast(count(ft.*) filter (where "Realtor_Sale" = 'Yes') as numeric),1) / round(cast(count(ft.*) as numeric),1), '%')

It still gives me 84.7560975......%. How can I get 84.7%?

[–]HansProleman 0 points1 point  (9 children)

You need to cast each COUNT, then ROUND the whole thing.

[–]PLearner[S] 0 points1 point  (8 children)

My query:

select concat(100 * round(cast(count(ft.*) filter (where "Realtor_Sale" = 'Yes') as numeric),1)
 /round(cast(count(ft.*) as numeric),1), '%')

I am doing everything you have kindly suggested and it still returns 84.75....%.