how to group by nama and filter with condition in mysql

Question

I have data table and i want to get datain based on date between, catagory and group by part id , i found problem when value part_id same

tblpart

part_id	category	ok	ng	total	datein
part 1	regullar	5	1	6	2023-01-02
part 2	beforeassy	4	0	4	2023-01-03
part 3	beforeassy	5	5	10	2023-01-03
part 4	newpart	5	0	5	2023-01-04
part 5	newpart	5	0	5	2023-01-02
part 6	newpart	5	0	5	2023-01-04
part 7	regullar	5	0	5	2023-01-05
part 8	beforeassy	5	0	5	2023-01-06
part 9	beforeassy	5	0	5	2023-01-08
part 10	beforeassy	5	0	5	2023-01-09
part 1	beforeassy	5	0	5	2023-01-08
part 3	regullar	5	0	5	2023-01-01
part 4	regullar	5	0	5	2023-01-03
part 5	beforeassy	5	0	5	2023-01-05
part 6	regullar	5	0	5	2023-01-06
part 7	regullar	5	0	5	2023-01-07
part 8	beforeassy	5	0	5	2023-01-08
part 9	regullar	5	0	5	2023-01-02
part 10	regullar	5	0	5	2023-01-02
part 1	beforeassy	12	1	14	2023-01-02
part 1	beforeassy	10	1	11	2023-01-03
part 1	beforeassy	12	2	14	2023-01-02

my query

CREATE TABLE `tblpart` (
  `id` int(11) NOT NULL,
  `part_id` varchar(23) NOT NULL,
  `category` varchar(23) NOT NULL,
  `ok` int(12) NOT NULL,
  `ng` int(12) NOT NULL,
  `total` int(12) NOT NULL,
  `datein` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `tblpart`
--

INSERT INTO `tblpart` (`id`, `part_id`, `category`, `ok`, `ng`, `total`, `datein`) VALUES
(1, 'part 1', 'regullar', 5, 1, 6, '2023-01-02'),
(2, 'part 2', 'beforeassy', 4, 0, 4, '2023-01-03'),
(3, 'part 3', 'beforeassy', 5, 5, 10, '2023-01-03'),
(4, 'part 4', 'newpart', 5, 0, 5, '2023-01-04'),
(5, 'part 5', 'newpart', 5, 0, 5, '2023-01-02'),
(6, 'part 6', 'newpart', 5, 0, 5, '2023-01-04'),
(7, 'part 7', 'regullar ', 5, 0, 5, '2023-01-05'),
(8, 'part 8', 'beforeassy', 5, 0, 5, '2023-01-06'),
(9, 'part 9', 'beforeassy', 5, 0, 5, '2023-01-08'),
(10, 'part 10', 'beforeassy', 5, 0, 5, '2023-01-09'),
(11, 'part 1', 'beforeassy', 5, 0, 5, '2023-01-08'),
(12, 'part 3', 'regullar', 5, 0, 5, '2023-01-01'),
(13, 'part 4', 'regullar', 5, 0, 5, '2023-01-03'),
(14, 'part 5', 'beforeassy', 5, 0, 5, '2023-01-05'),
(15, 'part 6', 'regullar', 5, 0, 5, '2023-01-06'),
(16, 'part 7', 'regullar', 5, 0, 5, '2023-01-07'),
(17, 'part 8', 'beforeassy', 5, 0, 5, '2023-01-08'),
(18, 'part 9', 'regullar', 5, 0, 5, '2023-01-02'),
(19, 'part 10', 'regullar', 5, 0, 5, '2023-01-02'),
(21, 'Part 1', 'beforeassy', 12, 2, 14, '2023-01-02'),
(22, 'Part 1', 'beforeassy', 10, 1, 11, '2023-01-03'),
(23, 'Part 1', 'beforeassy', 12, 2, 14, '2023-01-02');






SELECT part_id, category, sum(ok) as oke, sum(ng) as datang, sum(total) as total, datein 
FROM tblpart 
WHERE datein BETWEEN '2023-01-01' and '2023-01-07' 
GROUP BY part_id 
HAVING category ='regullar' or category ='newpart'  
ORDER by datein;

my query result

part id	category	ok	ng	total
part 1	regullar	39	6	45
part 10	regullar	5	0	5
part 9	regullar	5	0	5
part 5	newpart	10	0	10
part 6	newpart	10	0	10
part 4	newpart	10	0	10

I just want to sum dan menampilkan the same part_id based on the regullar and newpart category.

my expectacion : Part 1 not sum because category different

part id	category	ok	ng	total
part 1	regullar	5	1	6
part 10	regullar	5	0	5
part 9	regullar	5	0	5
part 5	newpart	10	0	10
part 6	newpart	10	0	10
part 4	newpart	10	0	10
part 3	regullar	5	0	5
part 7	regullar	10	0	10

Answer 1

The GROUP BY statement groups rows that have the same values into summary rows

e.g

part_id 7

Will have two rows:
------------------------------------------
part 7  regullar    5   0   5   2023-01-05
------------------------------------------
part 7  regullar    5   0   5   2023-01-07
------------------------------------------
And Part 7 count will be 2 and the ok will be 10, ng will be 0, the total will be 10

You can update your query to also group by the category

SELECT partId, count(partId) as partIdCount, category, sum(ok) as oke, sum(ng) as datang, sum(total) as total, datein
FROM tblpart 
WHERE datein BETWEEN '2023-01-01' and '2023-01-07' 
GROUP BY partId ,category 
HAVING category ='regullar' or category ='newpart' ORDER by datein;

Output