SQLite Forum

Question about Aggregate Function COUNT and Window Function nth_value
Login

Question about Aggregate Function COUNT and Window Function nth_value

(1) By Yu Liang (LY1598773890) on 2021-05-06 01:52:10 [source]

Hi all:

For query:


CREATE TABLE v0       ( v2 INT, v1 INT );
INSERT INTO v0 VALUES ( 1     , 10      );
INSERT INTO v0 VALUES ( 1     , 10      );
INSERT INTO v0 VALUES ( 1     , 3       );
INSERT INTO v0 VALUES ( 1     , 3       );

select rowid, v2, v1, nth_value(v1, 1) over () FROM v0 ORDER BY (COUNT());
/* This SELECT stmt outputs '1|1|10|10'  */

select rowid, v2, v1 FROM v0 GROUP BY v1 ORDER BY (COUNT());
/* This SELECT stmt outputs '3|1|3    1|1|10'   */

select rowid, v2, v1 FROM v0 ORDER BY (COUNT());
/* This SELECT stmt outputs 'Error: misuse of aggregate: COUNT()' */


We cannot fully understand the first SELECT statement. Although this output is highly possible to be expected, we do not understand why the Window Function nth_value is able to rectify the error proposed by COUNT() shown in the third SELECT statement.

We appreciate any explanation available to help us understand this first SELECT statement. Thank you.

(2) By Yu Liang (LY1598773890) on 2021-05-06 19:13:41 in reply to 1 [link] [source]

After double checking with the documentations of Window Function and Aggregate Function, we still do not understand the full picture of this query.

We sincerely appreciate if any explanation could be provided to help us further understand this first SELECT statement. Thank you.

(3) By Yu Liang (LY1598773890) on 2021-05-07 02:23:32 in reply to 2 [link] [source]

The questioned query being posted in this thread is related and being further extended in thread: https://sqlite.org/forum/forumpost/99d452c4a8. We thank for any explanation available for helping us better understand this first SELECT statement.

(4) By Yu Liang (LY1598773890) on 2021-05-07 21:33:19 in reply to 1 [link] [source]

It seems that the question is being answered in commit: https://sqlite.org/src/info/0d11d777c8d368f0. This COUNT() aggregate function will be blocked in the ORDER BY clause in the later version of SQLite.

Thank you for the fix and the information being posted in commit: https://sqlite.org/src/info/0d11d777c8d368f0.