How to group division in postgresql using sql

postgresql division decimal
postgres divide two columns
postgres how to use group by
postgresql group by having
postgresql sum count group by
postgres group by with join
postgresql multiply
postgres group by expression

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


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.

PostgreSQL find groups with same members, This is called relational division and your query is pretty much what you can do with standard SQL. A different way to write this in Postgres  Introduction to PostgreSQL GROUP BYclause The GROUP BYclause divides the rows returned from the SELECT statement into groups. For each group, you can apply an aggregate function e.g., SUM()to calculate the sum of items or COUNT() to get the number of items in the groups. The following statement illustrates the syntax of the GROUP BYclause:


demo:db<>fiddle

SELECT
    model,
    SUM((status = 'F')::int) || '/' || COUNT(*) AS fail_rate
FROM
    mytable
GROUP BY model
  1. (status = 'F') gives true, if status == 'F'. Otherwise it gives false.
  2. Casting a type boolean (true/false) into an int yields in 1 for true, 0 for false
  3. 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'
  4. COUNT(*) gives all records
  5. With the string concatenation operator || you can create your expected result.

Documentation: 8.0: Mathematical Functions and , division (integer division truncates results), 4 / 2, 2. %, modulo (remainder), 5 % 4​, 1. ^, exponentiation, 2.0 ^ 3.0, 8. |/, square root, |/ 25.0, 5. ||/, cube root, ||/ 27.0  SELECT 15 / 3 AS "Division"; Output: PostgreSQL Modulo ( % ) operator example. If we want to get the remainder of a division by two numbers, the following SQL can be used. SQL. Code: SELECT 15 % 2 AS "Remainder"; Output: PostgreSQL Exponentiation ( ^ ) operator example. If we want to get the exponentiation of a number powered by another number, the following SQL can be used. SQL. Code:


I did some research, get my version:

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

Re: Group by in DIVISION, JOHN_MCY wrote: > hai, > > I am new to SQL > > I am trying to get the result of some mathematical calculation with > group by some key. The PostgreSQL MOD() function performs the modulo operation that returns the remainder after division of the first argument by the second one. Syntax. The syntax of the PostgreSQL MOD() function is as follows: MOD(x,y) Arguments. The MOD() function requires two arguments: 1) x. The x argument is a number which is divided by the second one. 2) y


Documentation: 9.4: Aggregate Functions, Prev · Up, Chapter 2. The SQL Language, Next Aggregates are also very useful in combination with GROUP BY clauses. For example, we can get the  Source SQL must always return 3 columns, first being what to use for row header, second the bucket slot, and third is the value to put in the bucket. crosstab except for the example crosstab3 ..crosstabN versions return unknown record types.


PostgreSQL GROUP BY, The group by clause is used to divide the rows in a table into smaller groups that have the same values in the specified columns. This clause is used with a SELECT statement to combine a group of rows based on the values or a particular column or expression. SQL modulo ( % ) operator. The SQL MODULO operator returns the remainder (an integer) of the division. Example: To get the modulus of a division of 150 by 7 from the DUAL table, the following SQL statement can be used : SELECT 150%7; Output: Practice SQL Exercises. SQL Exercises, Practice, Solution ; SQL Retrieve data from tables [33 Exercises]


Deeper into Postgres 9.5, Let's look at the same simple COUNT(*) example but using GROUPING SETS in the GROUP BY clause instead: SELECT region, division  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(