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.
Thank you!
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;
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
