How can I return a pass percentage with joins?-CodePudding

I'm trying to return a quality pass rate that displays a % of total instances. So if my first statement returns 18 and my total is 29, I want to display 62.1.

SELECT COUNT(*) FROM EVENTS 
RIGHT JOIN EVENTATTRIBUTES ON EVENTS.EVENT_NUM = EVENTATTRIBUTES.EVENT_NUM 
WHERE EVENTS.EVENT_TYPE='QUALITY REVIEW'
AND (EVENTATTRIBUTES.ATTRIBUTE_NAME='QR PASS' AND EVENTATTRIBUTES.ATTRIBUTE_AS_CHAR='1');

SELECT COUNT(*) 
FROM EVENTS 
RIGHT JOIN EVENTATTRIBUTES ON EVENTS.EVENT_NUM = EVENTATTRIBUTES.EVENT_NUM 
WHERE EVENTS.EVENT_TYPE='QUALITY REVIEW' 
AND (EVENTATTRIBUTES.ATTRIBUTE_NAME='QR PASS');

The individual select statements work fine, but I've tried a ton of different ways to combine them into a single statement and my output isn't correct. Below is the closest I seem to get. It seems backwards but I haven't had much luck seeing what each piece is returning, which is making it difficult to trouble shoot. It's currently returning 5.555.

SELECT COUNT(*) * 100.0  / (SELECT COUNT(*) 
FROM EVENTS 
RIGHT JOIN EVENTATTRIBUTES ON EVENTS.EVENT_NUM = EVENTATTRIBUTES.EVENT_NUM 
WHERE EVENTS.EVENT_TYPE='QUALITY REVIEW'
AND (EVENTATTRIBUTES.ATTRIBUTE_NAME='QR PASS' AND EVENTATTRIBUTES.ATTRIBUTE_AS_CHAR='1'));

CodePudding user response：

You can place your queries as table expressions of a main query that can compute the percentage. For example:

select
  100.0 * a.cnt / b.cnt  
from (
  -- query 1 here
  select count(*) as cnt from events...
) a
cross join (
  .. query 2 here
  select count(*) as cnt from events...
) b

Or, you can compress it without using subqueries by using conditional aggregation. For example:

SELECT
  100.0 *
  sum(case when EVENTATTRIBUTES.ATTRIBUTE_AS_CHAR='1' then 1 else 0 end)
  /
  COUNT(*) 
FROM EVENTS 
JOIN EVENTATTRIBUTES ON EVENTS.EVENT_NUM = EVENTATTRIBUTES.EVENT_NUM 
WHERE EVENTS.EVENT_TYPE='QUALITY REVIEW'
AND EVENTATTRIBUTES.ATTRIBUTE_NAME='QR PASS'

Note: I changed the RIGHT JOIN to just JOIN since it's being silently converted to an inner join by the predicate EVENTS.EVENT_TYPE='QUALITY REVIEW'.