How to group division in postgresql using sql

Question

data rows:

id status model  time
1  F      m0     12:00
2  S      m0     13:00
3  F      m0     14:00
4  S      m1     12:00
5  S      m1     13:00
6  S      m1     14:00

I want to get fail rate(status =f) group by model, result will be

model    fail_rate
m0       2/3
m1       0/3

Can we get the result using sql only?

Thanks in advance

Does the result have to look like 2/3 or 0.66 or 66.66%?

Salman Arshad
– Salman Arshad

2019-10-08 14:58:51 +00:00
Commented Oct 8, 2019 at 14:58 — Salman Arshad
– Salman Arshad, Commented Oct 8, 2019 at 14:58
0.66 is good one, 2/3 just for good understand

jamee
– jamee

2019-10-08 15:23:34 +00:00
Commented Oct 8, 2019 at 15:23 — jamee
– jamee, Commented Oct 8, 2019 at 15:23

Gordon Linoff · Accepted Answer · 2019-10-08 14:55:39Z

1

Easily, using aggregation:

select model,
       avg( (status = 'F')::int ) as fail_rate
from t
group by model;

Postgres can convert a boolean expression to a number, with "1" for true and "0" for false. The average of these values is the rate.

answered Oct 8, 2019 at 14:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

jamee Over a year ago

what does avg means? why not status = 'F' / all ?

Gordon Linoff Over a year ago

@jamee . . . avg() is the aggregation function for "average". I have not idea what the rest of your comment means.

jamee Over a year ago

I undersand finally, avg is alternative method to get rate, that's very clever, S-Man clarify it

S-Man · Accepted Answer · 2019-10-08 15:27:48Z

0

demo:db<>fiddle

SELECT
    model,
    SUM((status = 'F')::int) || '/' || COUNT(*) AS fail_rate
FROM
    mytable
GROUP BY model

(status = 'F') gives true, if status == 'F'. Otherwise it gives false.
Casting a type boolean (true/false) into an int yields in 1 for true, 0 for false
Now you are able to sum up these integers. This gives you the sum of all 1 values, which is the count of all status == 'F'
COUNT(*) gives all records
With the string concatenation operator || you can create your expected result.

answered Oct 8, 2019 at 15:27

S-Man

24k9 gold badges51 silver badges78 bronze badges

2 Comments

jamee Over a year ago

Your comments is very understandful

S-Man Over a year ago

If you liked my answer, please do not forget to upvote. Upvoting is for all answers that are helpful although they might not be the one which solves the problem completely (what the accept is for). Thank you!

jamee · Accepted Answer · 2019-10-09 01:10:37Z

0

I did some research, get my version:

SELECT
    model,
    (COUNT(*) FILTER (WHERE status = 'F')::numeric / COUNT(*)) AS fail_rate
FROM
    mytable
GROUP BY model

answered Oct 9, 2019 at 1:10

jamee

5731 gold badge7 silver badges28 bronze badges

Collectives™ on Stack Overflow

How to group division in postgresql using sql

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related