Mysql miss count of string, bug?

Question

Here my mysql query to count the repeated item,

SELECT
  count(item),
  item,
  name,
  area
FROM jmr
WHERE starttime LIKE '2024-05-03%' and area='DC'
group by item, name;

Result:

count(item)	item	name	area
1	24050C24S	DC-STD-03	DC
2	24050C24S	DC-STD-04	DC
1	24050506S	DC-STD-01	DC
1	2405065ES	DC-STD-01	DC
1	2406025CS	DC-STD-01	DC
2	24050E98S	DC-STD-03	DC
1	24050E9BS	DC-STD-03	DC
1	24100258S	DC-STD-04	DC
1	24040A88S	DC-STD-04	DC
1	24050506S	DC-STD-02	DC

Base on above query and result, by right the item 24050506S should has 2 counts, but mysql miss 1 of it.

It is cause by my query or something not right with mysql ?

Answer 1

There are not inputs of table information...

According to query I tried to fix it. You want to count items, group by item & name where starttime is '2024-05-03' and area is 'DC'.

So, the query is:

SELECT
  count(item) over(partition by item) as count,
  item,
  name,
  area
FROM jmr
WHERE starttime LIKE '2024-05-03%' and area='DC'
group by item, name
order by item, name;

Output: db<>fiddle