SQL two tables distinct counts with join

Question

I have two tables.

Table 1 contains orders and customer codes. Table 2 contains orders with issue codes.

I need to be able to return distinct order count by customer from table 1 along with a distinct count by customer of orders with issuecode of 'F' from table 2. Then the final field will be a ratio of the two. Issue count / Order count. I'm using AS400/DB2 SQL. Any help would be greatly appreciated.

Customer ORcnt IScnt IssueRatio cust1 450 37 0.082 cust2 255 12 0.047 cust3 1024 236 0.230 cust4 450 37 0.082

Answer 1

You can use an outer join to your issues table and count with distinct. Something like this depending on your table definitions:

select o.customercode, 
       count(distinct o.orderid), 
       count(distinct i.orderid), 
       count(distinct i.orderid)/count(distinct o.orderid) ratio
from table1 o
      left join table2 i on o.orderid = i.orderid and i.issuecode = 'F'
group by o.customercode

Some databases would need to convert the ratio to a decimal -- I'm not sure about db2. If needed, one way is to multiply the result by 1.0:

1.0*count(distinct i.orderid)/count(distinct o.orderid)

Also, you may not need the distinct with the count -- depends on your data...