mysql count of a column based on date and type

Question

I have the following table optimizations

select items_type,
 created_at,
 count_optimized_items 
from optimizations 
where shop_id=126 
and count_optimized_items!=0

items_type	created_at	count_optimized_items
4	2023年04月20日 06:49:00	1
2	2023年04月20日 06:49:00	1
1	2023年04月20日 06:48:59	50
5	2023年04月20日 06:48:59	170
5	2023年04月21日 07:00:42	212
5	2023年04月20日 07:14:54	147
5	2023年04月20日 07:25:31	210
5	2023年04月20日 07:39:33	193
5	2023年04月20日 07:52:41	102

Now I want to group by and get sum of count_optimized_irems according to items_type and date

select name,
 shop_id,
 items_type,
 created_at,
 count_optimized_items,
 sum(count_optimized_items) as sum_count 
from metadata_optimizations 
where shop_id=126 
and count_optimized_items!=0 
group by created_at,items_type

I tried the above query but it does not work

this is the expected result

items_type	created_at	count_optimized_items
4	2023年04月20日 06:49:00	1
2	2023年04月20日 06:49:00	1
1	2023年04月20日 06:48:59	50
5	2023年04月20日 06:48:59	822
5	2023年04月21日 07:00:42	212

basically sum of count_optimized_items based on date and items type

Answer 1

The problem stands in the group by part. You need to group by DATE(created_at).

Consider the following data example which can be found in this fiddle as well

create table optimizations (
 items_type int,
 created_at timestamp ,
 count_optimized_items int );
insert into optimizations values
(4,'2023-04-20 06:49:00',1),
(2,'2023-04-20 06:49:00',1),
(1,'2023-04-20 06:48:59',50),
(5,'2023-04-20 06:48:59',170),
(5,'2023-04-21 07:00:42',212),
(5,'2023-04-20 07:14:54',147),
(5,'2023-04-20 07:25:31',210),
(5,'2023-04-20 07:39:33',193),
(5,'2023-04-20 07:52:41',102);

Query,

select items_type,
 DATE(created_at) as dt,
 sum(count_optimized_items) AS count_optimized_items_sum
from optimizations
group by items_type, DATE(created_at);

With the other conditions the final query would look as follows

select min(name) as name,
 min(shop_id) as shop_id,
 items_type,
 DATE(created_at) as created_at,
 sum(count_optimized_items) as sum_count 
from metadata_optimizations 
where shop_id=126 
and count_optimized_items !=0 
group by DATE(created_at),items_type