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

Reply via email to