Search code examples
sqlpostgresqlanalyticsdata-analysis

How to find percent of whole grouped by a value in PostgreSQL?

I have 2 tables

Table 1

| order_id | remade_or_not |    
| ---      | ---           |  
| 1        | true          |  
| 2        | false         |  
| 3        | true          |

Table 2

| order_id | maker_id |  
| ---      | ---      |  
| 1        | 101      |   
| 2        | 101      |  
| 3        | 102      |

How do I find the remade_or_not percentage for every maker_id in PostgreSQL? The two tables can be joined on the order_id.
For e.g., what is the percentage where remade_or_not = true for maker_id 101.

Solution

  • If remade_or_not is already a boolean column, then you can cast it to int and take the avg():

    select t2.maker_id, avg(t1.remade_or_not::int) as remade_pct
  from table_1 t1
       join table_2 t2 
         on t2.order_id = t1.order_id
 group by t2.maker_id;

    Working fiddle