Doh! Check the species is a rodent that is being matched!
Select * from species where geus = 'Rodent'; gives species_id 'UR' Swap that into the code and you get the correct answer, vis: SELECT surveys.plot_id, species.genus, count(species.genus) as observations FROM surveys JOIN species ON IFNULL(surveys.species_id,'UR') = 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: Amy E. Hodge <[email protected]> Sent: 21 October 2017 00:19 To: David Martin (Staff); [email protected] Subject: Re: SQL IFNULL question David, ‘AB’ is already a species, so I converted this to ‘XX’ so that they wouldn’t get dumped into the Amphispiza group. Yes, this does run, but your results should show 7 Rodents for plot_id 1, but only shows 1. The total count of all the genera for plot 1 is only 1989 and it should be 1995 because there are 6 NULL. I can’t sort out how to get this method to work, and, sadly, do not have any further time to devote to this. I loved when this was my job and I could get absorbed in these minutiae for hours! ~ 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 4:14 PM To: "Amy E. Hodge" <[email protected]>, "[email protected]" <[email protected]> Subject: Re: SQL IFNULL question 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 The University of Dundee is a registered Scottish Charity, No: SC015096
_______________________________________________ Discuss mailing list [email protected] http://lists.software-carpentry.org/listinfo/discuss
