skip to Main Content

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`
    )

Which outputs the following:
enter image description here

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`
;

Which outputs the following:
enter image description here

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


  1. I would simply run the 2 statements on the same line with a innerr join in the middle. That should solve it.

    Login or Signup to reply.
  2. I case that both queries the same sourc_id you would use a INNER JOIN

    So the result would be

    SELECT A.`Source ID`, A.`Numerator`, B. `Denominator`
    FROM
        (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 statements.`Source ID`, observations.`Relationship ID`
                )
                AS subsubquery
            WHERE `Ubiquity Percentage` < 10
            GROUP BY `Source ID`
        ) A
        INNER JOIN 
        (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 statements.`Source ID`, observations.`Relationship ID`
    )
    AS subquery
    GROUP BY `Source ID`
    ) B ON A.`Source ID` = B.`Source ID`
    ORDER BY `Source ID`
    

    If the soure_ids are not the same you need a FULL OUTER JOIN with mysql doesn’t provide

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search