0

I am trying to get a metric of my database of 'relationships' that are found in less than 10% of 'sources'. I have successfully created a table that has every relationship, as well as the corresponding percentage of sources each relationship is found in. Below is my code:

use rt_database;
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
#Used the code above to ensure ONLY_FULL_GROUP_BY mode is deactivated for my grouping

SELECT 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 observations.`Relationship ID`
#HAVING "Ubiquity Percentage" < 10 
#The line above is what I thought would successfully limit my table to only relationships less than 10%
ORDER BY observations.`Relationship ID`
;

The code above works perfectly in getting me a complete table with each relationship and it's corresponding percentage. However, when I uncomment the HAVING line, no changes are made to the table. And if I edit the HAVING line to say HAVING "Ubiquity Percentage" > 10 , then the table output is completely empty. Below is a screenshot of the current results I'm getting:

Current Resultant Table

Would love some help. Thank you!

Tyson Humphrey
  • 9
  • 4
  • You don't need to disable `ONLY_FULL_GROUP_BY` since there are no columns other than the one you're grouping by and the aggregated value. – Barmar May 09 '23 at 15:26
  • Use backticks, not double quotes, in the `HAVING` clause. You're comparing the literal string, not the column. – Barmar May 09 '23 at 15:27
  • And when you compare the string, it converts to `0`, and `0 < 10` is always true. – Barmar May 09 '23 at 15:29
  • @Barmar Thank you! So simple, I feel dumb for not catching that. That fixed the problem. – Tyson Humphrey May 09 '23 at 15:40
  • It's an understandable mistake if you learned standard SQL. Using double quotes for strings is a MySQL extension. – Barmar May 09 '23 at 15:41
  • @Barmar Do you happen to know how I can now edit the code to only show me the COUNT of all the percentages less than 10%, instead of showing me each one? – Tyson Humphrey May 09 '23 at 16:06
  • Wrap the whole query in `SELECT COUNT(*) FROM (your query) AS x` – Barmar May 09 '23 at 16:11
  • Note that with your having uncommented, it will generate a warning "Truncated incorrect DOUBLE value: 'Ubiquity Percentage'". https://dbfiddle.uk/bLuXCRnL When testing/debugging, do consider using a client that at least shows you when there are warnings; I like the mysql command line client. – ysth May 09 '23 at 16:39

0 Answers0