Yes is the answer having actually installed the software and data with which to play (possibly procrastinating from grading papers).
SELECT surveys.plot_id, species.genus, count(species.genus) as observations FROM surveys JOIN species ON IFNULL(surveys.species_id,'AB') = species.species_id GROUP BY surveys.plot_id, species.genus; Dr David Martin Senior Lecturer in Bioinformatics College of Life Sciences University of Dundee ________________________________ From: Discuss <[email protected]> on behalf of David Martin (Staff) <[email protected]> Sent: 21 October 2017 00:01 To: Amy E. Hodge; [email protected] Subject: Re: [Discuss] SQL IFNULL question I think there is a philosophical difference between your solution with a left join and building on the example which does an inner join, but that may not be important in the long run. It is a case of how you are phrasing the question. In your case you replicate the ifnull clause three times in the query wheras by including the ifnull in the join statement it is only present once and a single point of dependency (I think it is better on a DRY principle, others will have a different opinion) Did my variant work? Did you determine the syntax error in your earlier attempts? ..d Dr David Martin Senior Lecturer in Bioinformatics College of Life Sciences University of Dundee ________________________________ From: Amy E. Hodge <[email protected]> Sent: 20 October 2017 23:42 To: David Martin (Staff); [email protected] Subject: Re: SQL IFNULL question Yes! This works: SELECT surveys.plot_id, IFNULL(species.genus, 'Rodent'), COUNT(IFNULL(species.genus, 'Rodent')) FROM surveys LEFT JOIN species USING (species_id) GROUP BY surveys.plot_id, IFNULL(species.genus, 'Rodent'); 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 From: "David Martin (Staff)" <[email protected]> Date: Friday, October 20, 2017 at 3:08 PM To: "Amy E. Hodge" <[email protected]>, "[email protected]" <[email protected]> Subject: Re: SQL IFNULL question SELECT surveys.plot_id, IFNULL(species.genus, 'Rodent'), COUNT(IFNULL(species.genus, 'Rodent')) FROM surveys LEFT JOIN species USING (species_id) GROUP BY surveys.plot_id, species.genus; The University of Dundee is a registered Scottish Charity, No: SC015096 The University of Dundee is a registered Scottish Charity, No: SC015096
_______________________________________________ Discuss mailing list [email protected] http://lists.software-carpentry.org/listinfo/discuss
