I have a table like

and need to calculate a simple ratio along the lines of

for a group, calculate the ratio of matches to the total set of products

so for group 1 the ratio would be 1/6, or 16.67%, group 2 would be 1/2 or 50%

CodePudding user response：

You can use conditional aggregation to get the result you want. For example

select
  groupid,
  1.0 * sum(case when matches then numofproducts else 0 end) /
  sum(numofproducts) as ratio 
from t
group by groupid
order by groupid

Result:

 groupid  ratio                  
 -------- ---------------------- 
 1        0.16666666666666666667 
 2        0.50000000000000000000 

See running example at DB Fiddle.

CodePudding user response：

You can use the FILTER clause of count function clause to count only those where matches is true and use sum sunction to get the total num_of_products. From there is just a simple math operation. So:

with test (groupid, numofproducts,  matches) as
     (values (1, 5, false)
           , (1, 1, true)
           , (2, 1, true)
           , (2, 1, false)
     )
-- your query starts here
select groupid, round( ( 100.0*count(*) filter (where matches)/sum(numofproducts)),2)  pct_matches
  from test
 group by groupid;

CodePudding user response：

You can do it with conditional aggregation with SUM() aggregate function if you use a FILTER clause:

SELECT groupid,
       ROUND(100.0 * SUM(numofproducts) FILTER (WHERE matches) / SUM(numofproducts), 2) ratio
FROM tablename
GROUP BY groupid
ORDER BY groupid;

I assume that the data type of the column matches is BOOLEAN.
If it is VARCHAR then change the WHERE clause to: WHERE matches = 'true'.

If there is a case that there are not any true values in the column matches for a groupid then you should also use COALESCE() to get 0 instead of null as result:

SELECT groupid,
       ROUND(100.0 * COALESCE(SUM(numofproducts) FILTER (WHERE matches), 0) / SUM(numofproducts), 2) ratio
FROM tablename
GROUP BY groupid
ORDER BY groupid; 

See the demo.