I want to get counts for each unique value in a field “foo”. The results should look like this:
[
{
"a": 146716,
"b": 16667,
"c": 12
}
]
I could get the results above using something like this but this would require me to hardcode all the possible values.
SELECT COUNT(CASE WHEN foo="a" THEN 1 ELSE NULL END) a,
COUNT(CASE WHEN foo="b" THEN 1 ELSE NULL END) b,
COUNT(CASE WHEN foo="c" THEN 1 ELSE NULL END) c,
COUNT(CASE WHEN foo="d" THEN 1 ELSE NULL END) d,
COUNT(CASE WHEN foo="e" THEN 1 ELSE NULL END) e,
COUNT(CASE WHEN foo="f" THEN 1 ELSE NULL END) f,
...
FROM bucket
I know I could do it like this: SELECT COUNT(*), foo FROM bucket GROUP BY foo
but this results in multiple objects instead of a single object.
The result with the CASE query is perfect but is there any way I could achieve the same result without hard-coding the values?