On Tuesday, 22 October, 2019 23:24, Doug <dougf....@comcast.net> wrote:
>Please explain one phrase in the select: > total(1) over (partition by city_txt, mode_txt) >Is "total" a function and does "1" refer to city_txt? >I can't wrap my head around what's going on with this phrase. total() is a built-in aggregate like sum() -- there are just some minor (but important) differences. sum() returns NULL if there was nothing to sum. Also, if the values were all integers and the result fits in an integer, an integer is returned. total() returns 0.0 if is has nothing to total, and the returned value is always floating-point. So total(1) means the floating-point value of adding up all the 1 values ... that it, it is count(*) in floating-point, or basically cast(count(*) as float) but somewhat shorter. This is because the integer expression 37/154 has the integer result 0, however the floating point expression 37.0 / 154.0 has result 0.24025974025974 -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
