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

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.

CodePudding user response：

try like below using conditional aggregation I assume remade_or_not is string data type in case it is bool then condition will be changed in side case

select maker_id,
(coalesce(sum(case when remade_or_not='true' then 1 else 0),0)*1.00/count(order_id))*100
    from   tab1 t1 jon tab2 t2 on t1.order_id=t2.order_id group by maker_id

CodePudding user response：

You take the count of orders by maker id and remade or not value and divide it by the total count of orders by maker id. If you want this expressed as a decimal between 0 and 100, change the 1.0 to 100.0.

SELECT x.maker_id
     , y.remade_or_not
     , COUNT(*) * 1.0 / COUNT(*) OVER (PARTITION BY x.maker_id) AS pct
FROM table2 x
     INNER JOIN table1 y ON x.order_id = y.order_id
GROUP BY x.maker_id
       , y.remade_or_not;