First time I have seen it. Try https://www.w3schools.com/sql/sql_isnull.asp
and wrap the species_id in ifnull() so select count(distinct(s.genus)) from surveys as o inner join species as s on ifnull(o.species_id, 'RN') = s.species_id; The tables are joined so every observation has a species, then you can reduce it to a single instance for each genus with distinct and count the number of rows with count( ) (iirc - it is a while since I have done this). SQL ISNULL(), NVL(), IFNULL() and COALESCE() Functions<https://www.w3schools.com/sql/sql_isnull.asp> www.w3schools.com Well organized and easy to understand Web building tutorials with lots of examples of how to use HTML, CSS, JavaScript, SQL, PHP, and XML. Dr David Martin Senior Lecturer in Bioinformatics College of Life Sciences University of Dundee ________________________________ From: Discuss <[email protected]> on behalf of Amy E. Hodge <[email protected]> Sent: 20 October 2017 21:59 To: [email protected] Subject: [Discuss] SQL IFNULL question Hi all, I’ll be teaching from http://www.datacarpentry.org/sql-ecology-lesson/03-sql-joins-aliases/ soon, and realized there is a section here on IFNULL and NULLIF that I haven’t taught before. In all my years of using SQL, I’ve never even heard of these functions! So, I’m trying to learn this quickly. SQL for Ecology: Joins and aliases - Data Carpentry<http://www.datacarpentry.org/sql-ecology-lesson/03-sql-joins-aliases/> www.datacarpentry.org Employ joins to combine data from two tables. Apply functions to manipulate individual values. Employ aliases to assign new names to items in a query. To combine data ... I cannot seem to wrap my brain around how to do this Challenge, and there is no answer key. (Also, plural of “genus” is “genera”!) * Write a query that returns the number of genus of the animals caught in each plot, using IFNULL to assume that unknown species are all of the genus “Rodent”. I’m at a loss as to how to replace NULL values in genus field with a value based on what is in the species_id field. Can someone help me out? Thanks! ~ Amy Amy E. Hodge, PhD Science Data Librarian [email protected]<mailto:[email protected]> 650.556.5194 [cid:[email protected]] orcid.org/0000-0002-5902-3077<https://orcid.org/0000-0002-5902-3077> Data Management Services Branner Earth Sciences Library, 212 Mitchell 397 Panama Mall; MC 2211 Stanford University Stanford, CA 94305 The University of Dundee is a registered Scottish Charity, No: SC015096
_______________________________________________ Discuss mailing list [email protected] http://lists.software-carpentry.org/listinfo/discuss
