I use group by 99,99% with aggregate functions.
Just this one special case I have when I must
get min, max values and also I have group_concat
where I must filter records. Result is then
inserted into table where I have not null constraint
and I get error because min, max returned null.
I solved this with two sql-s. First is insert
and second is update.
I didn't know I could use dummy value for
grouping. I see this is not just sqlite
specific. I tried also with Firebird DB
and it works. So I will modify my program.
Clemens Ladisch je 07.02.2017 ob 8:40 napisal:
> Radovan Antloga wrote:
>> select min(A)
>> from TEST
>> where B is null
>> and A > 3;
>> if you replace min(A) with * you get empty result set as expected
>> but with min or max or avg you get one record
> This is just how aggregate functions in SQL work.
> When you're using GROUP BY, you get exactly one result row per group.
> And if there are no rows that are grouped, there are no groups, and
> therefore the result is empty.
> When you're not using GROUP BY, you always get exactly one result row
> out of the aggregate function(s), even if they aggregate an empty set.
> If you do want an empty result if the (filtered) source table is empty,
> you have to add grouping (by some dummy value):
> SELECT min(a)
> FROM Test
> WHERE b IS NULL
> AND a > 3
> GROUP BY NULL;
> sqlite-users mailing list
> [hidden email] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users