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
