-1

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
garikarta
  • 1
  • 2
  • Provide desired output for shown data with detailed explanations (the value for column .. in the output row .. is .. because ..). Also add complete CREATE TABLE script. – Akina Apr 14 '23 at 05:00
  • hii akina thanks for respond, – garikarta Apr 14 '23 at 06:00
  • Why your desired output does not contain the rows for 'part 3' and 'part 7'? Why for the row for 'part 5' you add the values taken from the source row with id=14 and category='beforeassy', but ignores similar rows (id=21,22,23) for 'part 1'? – Akina Apr 14 '23 at 07:58
  • yes including part 3 & part 7, sorry I missed it. – garikarta Apr 14 '23 at 08:15
  • Where? edit your question and make your desired output completely matched to sample data. Also sync sample data in table form and in SQL script - now they differs. Also answer 2nd question in my previous comment. – Akina Apr 14 '23 at 08:35
  • @Akina please check my update, answer question 2nd this is same category i'm typo – garikarta Apr 14 '23 at 08:51
  • In edited src data and desired output: where the value 10 for 'part 5' into 'ok' and 'total' is taken from? point me to definite source `id` values which are taken into account.. and the same for 'part 7'. – Akina Apr 14 '23 at 09:45

1 Answers1

0

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 enter image description here

Dingaan Letjane
  • 35
  • 6