SQLite is special WRT to non-aggregate columns in aggregate queries. Most other engines will not allow columns that are neither group fields nor aggregated fields. SQLite OTOH does, and it promises that these fields are "related" to the group tuple.
Consider: CREATE TABLE letters (type integer, letter text); INSERT INTO letters VALUES (1,'a'), (1,'e'), (1,'i'), (1,'o'), (1, 'u'); INSERT INTO letters VALUES (2,'b'), (2,'d'), (2,'f'), (2,'h'), (2, 'k'), (2,'l'),(2,'t'); INSERT INTO letters VALUES (3,'g'), (3,'j'), (3,'p), (3,'q'), (3,'y'); SELECT type,letter FROM letters GROUP BY type; This will return three rows, with one "random" (determined by the inner workings) letter from each type. SELECT type,MIN(letter) FROM lettters GROUP BY type; This will return the "smallest" letter for each group. Both are aggregate queries, even if the first one does not contain an aggregate expression. Adding more columns to both the table and the column list will have the additional values taken from the same row; in the case of exactly one MIN() or MAX() expression, from one "random" row where the column value matches the value returned for that group. Re user defined group function: SELECT a,b,c,MIN(d),my_func(a,b,c) FROM table GROUP BY 1,2,3; Even though this will give you access to the current values of the group fields, it does not allow you to examine the current row (unless you requery it from inside the xStep method, or you pass along a "magic" value (e.g. reference to in-memory image of the current row from a virtual table). -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Stephan Buchert Gesendet: Montag, 29. Jänner 2018 18:09 An: sqlite-users@mailinglists.sqlite.org Betreff: [EXTERNAL] [sqlite] Groups in C API Ok, I should have tested this before asking. I had assumed that "If the SELECT statement is *a non-aggregate query*, then each expression in the result expression list is evaluated for each row in the dataset filtered by the WHERE clause" on https://www.sqlite.org/lang_select.html#resultset means, that, if it is a non-aggregate query, I would get every row regardless whether there is a GROUP BY or not. But actually the description after *Side note: Bare columns in an aggregate queries.* ... applies also to non-aggregate queries: I get exactly one arbitrary row in each group. But then I have a related question: to get my hands on each row in SELECTs with GROUP BY I could write an aggregate extension function. How do I see there, when a group ends and a new one starts? I.e. How do I implement the xStep and xFinal C functions as requested at https://sqlite.org/c3ref/create_function.html The built in SQL avg function returns the average in each group. How would I implement say variance, skewness, ... functions that do the same? _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users