I have a simple dataset that has times for a script to complete various steps. The times are unpredictable but grouped mostly in a specific time range however I would like to graph this in groupings of 10ths of a second. (I know it’s weird, it’s a requirement of some report visualization stuff). I could just extend my case statement to be insanely long but I assume there is a better method to do this. What is that method?
SELECT
COUNT(thing) AS value,
CASE
WHEN (COALESCE(step1,0)+COALESCE(step2,0)+COALESCE(step3,0)+COALESCE(step4,0)+COALESCE(step5,0)+COALESCE(step6,0)+COALESCE(step7,0)) BETWEEN 0 AND 0.1 THEN '0-0.1'
WHEN (COALESCE(step1,0)+COALESCE(step2,0)+COALESCE(step3,0)+COALESCE(step4,0)+COALESCE(step5,0)+COALESCE(step6,0)+COALESCE(step7,0)) BETWEEN 0.1 AND 0.2 THEN '0.1-0.2'
WHEN (COALESCE(step1,0)+COALESCE(step2,0)+COALESCE(step3,0)+COALESCE(step4,0)+COALESCE(step5,0)+COALESCE(step6,0)+COALESCE(step7,0)) BETWEEN 0.2 AND 0.3 THEN '0.2-0.3'
WHEN (COALESCE(step1,0)+COALESCE(step2,0)+COALESCE(step3,0)+COALESCE(step4,0)+COALESCE(step5,0)+COALESCE(step6,0)+COALESCE(step7,0)) BETWEEN 0.3 AND 0.4 THEN '0.3-0.4'
WHEN (COALESCE(step1,0)+COALESCE(step2,0)+COALESCE(step3,0)+COALESCE(step4,0)+COALESCE(step5,0)+COALESCE(step6,0)+COALESCE(step7,0)) BETWEEN 0.4 AND 0.5 THEN '0.4-0.5'
-- ad infinitum
END AS metric
FROM table
GROUP BY metric
Answer
Using ROUND and group by that column is good enough for me, if someone has a more interesting answer that allows things to be more arbitrary (like if it was an int and not a float or if buckets could be sized arbitrarily) I would love to see that and will mark it as an answer.
SELECT COUNT(thing) AS value, ROUND(COALESCE(step1,0)+COALESCE(step2,0)+COALESCE(step3,0)+COALESCE(step4,0)+COALESCE(step5,0)+COALESCE(step6,0)+COALESCE(step7,0),1) AS metric FROM table GROUP BY metric