I apologize in advance since I am somewhat new to SQL so my code may seem like it makes no sense at all, but I will try to make as much sense of it as I can and hopefully you more experienced people can help me out. I'm not sure if it will help, but I am using an Oracle database.
I have two tables:
Elements - This contains an elementID as well as an elementName field
Exchanges - This contains an exchangeID and an elementID field
I created the following query in order to retrieve only the elements that occur in more than 50% of the exchanges:
SELECT e.elementName
FROM Elements e
WHERE e.elemID in
(SELECT elemID FROM Exchanges
GROUP BY elemID
HAVING (count(*) / (SELECT count(*) FROM
(SELECT exchangeID, count(*) FROM Exchanges GROUP BY exchangeID))) > 0.5);
This part seems to be working fine, but I ALSO need to retrieve the % of occurrences for each item in exchanges. So, for example, if an element A occurs in 76% of exchanges and an element B occurs in 59% of exchanges, I would want to return a row with A and 76% and another row with B and 59%. In my mind, this would mean somehow getting the result of the expression on the left side of the HAVING clause:
(count(*) / (SELECT count(*) FROM
(SELECT exchangeID, count(*) FROM Exchanges GROUP BY exchangeID)))
This expression gives me the percentage I am looking for, but I haven't been able to figure out a way to use this expression in such a way that I can retrieve it with a SELECT so that I can pair it up with its corresponding elementName in a way like I just explained.
Is there a way to propagate this value up to my SELECT so that I can retrieve the value I am looking for?
You will want to pre-aggregate two derived tables (1) the exchanges per-element, and (2) the total number of exchanges. The rest become elementary.
SELECT x.elementName, A.elemExchanges/B.totalExchanges Percentage
FROM
(
SELECT x.elemID,
count(distinct x.exchangeID) elemExchanges
FROM Exchanges x
GROUP BY x.elemID
) A
CROSS JOIN
(
SELECT COUNT(distinct y.exchangeID) totalExchanges
FROM Exchanges y
) B
JOIN Elements e on e.elemID = A.elemID
WHERE A.elemExchanges/B.totalExchanges > 0.5;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With