Background: I have a number of sources that reference a relationship. I have calculated the percentage of the sources in which any particular relationship is found (since multiple sources sometimes reference the same relationship). Any relationship that is found in less than 10% of the sources is considered a "unique relationship".
I now want to find the metric for each source that is the ratio of the amount of unique relationships found in that source over the total number of relationships found in that source.
I have successfully obtained a table for the numerator values with the following code:
(SELECT `Source ID`, COUNT(`Source ID`)
AS "Numerator"
FROM
(
SELECT statements.`Source ID`, observations.`Relationship ID`,
ROUND((COUNT(statements.`Source ID`) /
(SELECT COUNT(sources.`Source ID`) FROM sources)),3) * 100
AS "Ubiquity Percentage"
FROM statements
JOIN observations ON statements.`Old Observation ID` = observations.`Old Obs ID`
GROUP BY `Relationship ID`
ORDER BY `Relationship ID`
)
AS subsubquery
WHERE `Ubiquity Percentage` < 10
GROUP BY `Source ID`
ORDER BY `Source ID`
)
I have also successfully obtained a table for the denominator values with the following code:
SELECT `Source ID`,
#`Ubiquity Percentage`,
COUNT(`Source ID`)
AS "Denominator"
FROM
(
SELECT statements.`Source ID`, observations.`Relationship ID`,
ROUND((COUNT(statements.`Source ID`) /
(SELECT COUNT(sources.`Source ID`) FROM sources)),3) * 100
AS "Ubiquity Percentage"
FROM statements
JOIN observations ON statements.`Old Observation ID` = observations.`Old Obs ID`
GROUP BY `Relationship ID`
ORDER BY `Relationship ID`
)
AS subquery
GROUP BY `Source ID`
ORDER BY `Source ID`
;
But I am unsure how to combine these into a single table, because when I try to run both under a single select, I get error 1242 that says the queries return more than one row and cannot be combined.
2
Answers
I would simply run the 2 statements on the same line with a innerr join in the middle. That should solve it.
I case that both queries the same sourc_id you would use a INNER JOIN
So the result would be
If the soure_ids are not the same you need a FULL OUTER JOIN with mysql doesn’t provide