StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POcount concatenate column in mysql
text
Body
copied!<p>I want do like <a href="https://stackoverflow.com/questions/3809375/counting-null-data-in-mysql">this</a>, but for another case that not function. This is my example table:</p> <pre><code>Date Model No Line Range Lot Status 2010-08-01 KD-G435 1 01 1-100 013A accept 2010-08-01 KD-G435 2 01 1-100 013A accept 2010-08-01 KW-TC800 1 01 1-200 001A null 2010-08-01 KW-TC800 2 01 1-200 001A null 2010-08-01 KW-TC800 3 01 1-200 001A null 2010-08-01 KD-R411 1 05 1-100 021A reject 2010-08-01 KD-R411 2 05 1-100 021A reject CREATE TABLE IF NOT EXISTS `inspection_report` ( `id` int(11) NOT NULL AUTO_INCREMENT, `Model` varchar(14) NOT NULL, `Serial_number` varchar(8) NOT NULL, `Lot_no` varchar(6) NOT NULL, `Line` char(5) NOT NULL, `Shift` char(1) NOT NULL, `Inspection_datetime` datetime NOT NULL, `Range_sampling` varchar(19) NOT NULL, `Packing` char(2) NOT NULL, `Accesories` char(2) NOT NULL, `Appearance` char(2) NOT NULL, `Tuner` char(2) NOT NULL, `General_operation` char(2) NOT NULL, `Remark` text NOT NULL, `NIK` int(5) NOT NULL, `S` int(11) NOT NULL, `A` int(11) NOT NULL, `B` int(11) NOT NULL, `C` int(11) NOT NULL, `Status` varchar(6) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `Model` (`Model`,`Serial_number`,`Lot_no`,`Line`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=84 ; -- -- Dumping data for table `inspection_report` -- INSERT INTO `inspection_report` (`id`, `Model`, `Serial_number`, `Lot_no`, `Line`, `Shift`, `Inspection_datetime`, `Range_sampling`, `Packing`, `Accesories`, `Appearance`, `Tuner`, `General_operation`, `Remark`, `NIK`, `S`, `A`, `B`, `C`, `Status`) VALUES (79, 'KD-G435UND', '135X0002', '012A', 'FA 01', 'A', '2010-08-01 14:26:35', '135X0001-135X0100', 'OK', 'OK', 'NG', 'OK', 'OK', '2ver-m302', 25158, 0, 1, 1, 0, 'accept'), (78, 'KD-G435UND', '135X0001', '012A', 'FA 01', 'A', '2010-08-01 14:24:35', '135X0001-135X0100', 'OK', 'OK', 'NG', 'OK', 'OK', '2ver-m302', 25158, 0, 0, 1, 0, 'accept'), (77, 'KW-TC800UND', '135X0003', '011A', 'FA 01', 'A', '2010-08-01 09:12:01', '135X0001-135X0100', 'OK', 'OK', 'OK', 'OK', 'OK', 'TEST', 25158, 0, 0, 0, 0, ''), (76, 'KW-TC800UND', '135X0002', '011A', 'FA 01', 'A', '2010-08-01 09:10:01', '135X0001-135X0100', 'OK', 'OK', 'OK', 'OK', 'OK', 'TEST', 25158, 0, 0, 0, 0, ''), (75, 'KW-TC800UND', '135X0001', '011A', 'FA 01', 'A', '2010-08-01 09:08:01', '135X0001-135X0100', 'OK', 'OK', 'OK', 'OK', 'OK', 'TEST', 25158, 0, 0, 0, 0, ''), (63, 'KD-R411ED', '135X0001', '022A', 'FA 05', 'A', '2010-08-01 16:24:04', '135V0001-135V0200', 'OK', 'OK', 'NG', 'OK', 'OK', 'ver-r105', 25158, 0, 1, 0, 0, 'reject'), (65, 'kd-r411ed', '135x0002', '022a', 'FA 05', 'a', '2010-08-01 09:08:01', '135v0001-135v0200', 'ok', 'ok', 'ng', 'ng', 'ok', 'ver-r105', 25158, 0, 1, 1, 0, 'reject'), (66, 'KD-G435UND', '135X0001', '023A', 'FA 05', 'A', '2010-09-02 14:24:35', '135X0001-135X0100', 'OK', 'OK', 'NG', 'OK', 'OK', '2ver-m302', 25158, 0, 0, 1, 0, 'accept'), (67, 'KW-TC800UND', '135X0001', '025A', 'FA 07', 'A', '2010-10-01 09:08:01', '135X0001-135X0100', 'OK', 'OK', 'OK', 'OK', 'OK', 'TEST', 25158, 0, 0, 0, 0, ''), (80, 'KD-G435UND', '135X0001', '013A', 'FA 02', 'A', '2010-09-01 14:24:35', '135X0001-135X0200', 'OK', 'OK', 'NG', 'OK', 'OK', '2ver-m302', 25158, 0, 0, 1, 0, 'accept'), (81, 'KD-G435UND', '135X0002', '013A', 'FA 02', 'A', '2010-09-01 14:28:35', '135X0001-135X0200', 'OK', 'OK', 'OK', 'OK', 'OK', '2ver-m302', 25158, 0, 0, 0, 0, ''), (82, 'kd-r411ed', '135x0002', '014a', 'fa 03', 'a', '2010-09-01 09:08:01', '135v0001-135v0200', 'ok', 'ok', 'ng', 'ng', 'ok', 'ver-r105', 25158, 0, 1, 1, 0, 'reject'), (83, 'KD-R411ED', '135X0001', '015A', 'FA 05', 'A', '2010-09-01 16:24:04', '135X9901-135V0000', 'OK', 'OK', 'NG', 'OK', 'OK', 'ver-r105', 25158, 0, 1, 0, 0, 'reject'); </code></pre> <h2>EDIT</h2> <p>I've tried this query:</p> <pre><code>SELECT Date(Inspection_datetime), Model, COUNT(DISTINCT(CONCAT(Range_sampling,Model,Line,Lot_no))) AS lot_qty, IF(Status !='reject',1,0) AS accept, IF(Status ='reject',1,0) AS reject </code></pre> <p>FROM inspection_report GROUP BY Date(Inspection_datetime), Model</p> <p>and get result like:</p> <pre><code>Date(Inspection_datetime) Model lot_qty accept reject 2010-08-01 KD-G435UND 1 1 0 2010-08-01 kd-r411ed 1 0 1 2010-08-01 KW-TC800UND 1 1 0 2010-09-01 KD-G435UND 1 1 0 2010-09-01 kd-r411ed 2 0 1 2010-09-02 KD-G435UND 1 1 0 2010-10-01 KW-TC800UND 1 1 0 </code></pre> <p>I want to make a table like:</p> <pre><code>Date lot_qty accept reject 2010-08-01 3 2 1 //count in same date become one 2010-09-01 3 1 1 //count in same date become one 2010-09-02 1 1 0 2010-10-01 1 1 0 </code></pre> <p>this query almost near the answer, but i can't count the accept and reject result then grouping in same date.try my query.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload