CODEWITHSUNDEEP
sqlpostgresql
Chris90
Chris90

Reputation: 1998

Rounding to eliminate decimal

I have query below as:

SELECT ds, 
       round(count(CASE WHEN action = 'accepted' THEN 1 
             ELSE NULL END) * 1.0 / count(action),2) * 100 as perc
FROM friend_requests
group by 1

Output:

perc
50.00
100.00
50.00

desired output:

perc
50
100
50

If I want to remove decimals and just have 50 or 100 how can I edit this? I try changing the ,2 parameter in round to ,0 and it just rounds all values to 100

Thanks

Upvotes: 0

Views: 55

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

I would suggest phrasing the logic as:

SELECT ds, 
       round(avg( (action = 'accepted')::int) * 100), 0) as perc
FROM friend_requests
GROUP BY 1

Upvotes: 1

gbeaven
gbeaven

Reputation: 1800

Cast your perc field as an int type and remove the round function:

SELECT
    ds,
    cast(count(CASE WHEN action = 'accepted' THEN 1 ELSE NULL END) * 1.0 / count(action) * 100 as int) as perc
FROM friend_requests
group by 1

Upvotes: 1

Related Questions