How to group division in postgresql using sql
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:
SELECT model, SUM((status = 'F')::int) || '/' || COUNT(*) AS fail_rate FROM mytable GROUP BY model
(status = 'F')gives
status == 'F'. Otherwise it gives
- Casting a type boolean (
false) into an int yields in
- Now you are able to sum up these integers. This gives you the sum of all
1values, which is the count of all
status == 'F'
COUNT(*)gives all records
- 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(