Question 21966 in Mysql Question posted in
The official MySQL documentation can be found here.

How to count values within a range in my MySQL table

Hello i im trying to count the values within each range e.g. between 115000 – 120000 in my DURATION_IN_MS column.

my column looks like this:

119631
120689
143498
119798

WITH tab1 AS(
SELECT TOP 30 * 
FROM MACHINE_PROCESSING_DURATION_EVALUATION
WHERE START_TIMESTAMP BETWEEN '2022-11.10 00:00:00.000' AND '2022-11.10 22:00:00.000')


SELECT(
  case 
    when DURATION_IN_MS BETWEEN 115000 AND 120000 THEN '115000-120000'
    when DURATION_IN_MS BETWEEN 120000 AND 125000 THEN '120000-125000'
    else 'OTHERS'
    END) AS DURATION_IN_MS, 
    COUNT(*) AS cnt
from tab1
GROUP BY DURATION_IN_MS



my output is like this: 


[enter image description here][1]

however, for the range 115000-120000 i wanted to  show the count of 8
for the range 120000-125000 i wanted to  show the count of 6

Can someone help me ?
Tags:

2

Answers


  1. 0

    ref: fiddle

    SELECT
  case 
      when DURATION_IN_MS BETWEEN 115000 AND 120000 THEN '115000-120000'
      when DURATION_IN_MS BETWEEN 120001 AND 125000 THEN '120001-125000'
      else 'OTHERS'
  END AS grp, 
  COUNT(*) AS cnt
from tab1
GROUP BY grp

    Like ysth said in comments, separate the ranges.

    The case statement defines the categories as grp, we GROUP BY this and the amount count(*) is per group.

    Login or Signup to reply.
  2. 0

    See if you like this technique:

    SELECT
    FLOOR(DURATION_IN_MS / 5000)     * 5000     AS 'bucket start',
    FLOOR(DURATION_IN_MS / 5000 + 1) * 5000 - 1 AS 'bucket end',
    COUNT(*) AS cnt
FROM MACHINE_PROCESSING_DURATION_EVALUATION
WHERE ...
GROUP BY 1, 2
ORDER BY cnt DESC
LIMIT 30;

    It does not match your question exactly but is more flexible in because it automatically creates all the necessary ranges. (Not WITH is needed.)

    To get an "other", I might UNION with another query that inverts it. (This is messier.)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search