Does anyone have any working solutions for calculating the mode of a set of values in SQLite?
In "SQL For Smarties", Celko gives two solutions, neither of which seem to work in SQLite: 1) SELECT salary, COUNT(*) AS frequency FROM Payroll GROUP BY salary HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM Payroll GROUP BY salary); This won't work because the ALL operator isn't supported. I know the ALL operator was discussed a while back on this list but no conclusion was reached that it would be added. Kurt Welgehausen suggested that the transformation x <op> ALL (SELECT y FROM t WHERE ...) to NOT EXISTS (SELECT y FROM t WHERE NOT (x <op> y) AND ...) would work around it, but as far as I can see this will only work in a WHERE clause and not a HAVING clause. 2) WITH (SELECT salary, COUNT(*) FROM Payroll GROUP BY salary) AS P1 (salary, occurs) SELECT salary FROM P1 WHERE P1.occurs = (SELECT MAX(occurs) IN P1); This won't work because derived tables aren't supported. I could create a temporary table outside the select, but I can't do this within a trigger. Regards, Tim