On Sun, Jan 31, 2016 at 9:19 AM, Michael Torrie <torr...@gmail.com> wrote: > On 01/30/2016 03:06 PM, Chris Angelico wrote: >> That actually violates the SQL spec. Some servers will accept it, >> others won't. (You're not supposed to mix column functions and >> non-column functions.) > > Are you sure? Wikipedia is not always the most accurate place, but they > have several clear examples on the SQL page of combining table fields > with count() listed. This is straight SQL we're talking about here, not > a particular implementation or dialect. Maybe there're some subtleties > at play here.
Here's some info: http://stackoverflow.com/questions/5920070/why-cant-you-mix-aggregate-values-and-non-aggregate-values-in-a-single-select I don't have a good spec handy, but dig around a bit with a few different engines and you'll find that some fully-compliant engines disallow this. >> It also can't cope with 'group by' queries, as >> it'll count the underlying rows, not the groups. I also suspect it >> can't handle join queries. > > The Wikipedia entry on SQL, which seems to be based in some grounding of > the spec, shows that count(), joins, and group by are all compatible > with each other. So I dunno! Yes, they are - but not with the semantics you're looking for. If you say something like this: select count(*), foo from table group by foo then you get one row for each unique foo, with its own count. It won't tell you how many rows are in the result. >> The original approach is still the most general, and IMO the best. > > Could be. On the other hand, letting the DB do it all solves his > problem without mucking about with async iterators. Except that it means mucking about with other things :) ChrisA -- https://mail.python.org/mailman/listinfo/python-list