Mysql query to select Distinct Records on conditions?

Question

i have table structure like this.. ext_no , value .. i want to select distinct records on condition..like when count of ext_no is more than two and IF AND ONLY IF all that ext_no value is zero.. I Want Expected Result Given below...like.. how to write mysql query this this..? any help would be appreciated.. Thanks in advance..

Table Structure:

CREATE TABLE `test` (
  `ext_no` int(5) default NULL,
  `value` int(3) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `test`
--

INSERT INTO `test` (`ext_no`, `value`) VALUES
(12133, 0),
(12133, 0),
(12133, 0),
(22222, 0),
(44226, 0),
(44226, 0),
(44226, 1),
(44226, 2),
(99902, 1),
(99902, 2),
(99902, 3),
(11505, 0),
(11505, 0),
(11505, 0),
(11505, 0);

Expected Result:

ext_no  value
12133     0
11505     0

Edit: i Tried-

select distinct ext_no, value from test where value ='0' order by ext_no DESC;

What have you tried??? SO is not a free coding service site. You post your query. If it doesn't work, we can try to help you. — Eric, Oct 16 '18 at 16:43
@Eric how to modify this answer.. if i want to select all that records having value zero.. i mean not distinct or group by..i want to select all records..that having value zero only... — Sachin_Bhagat_Ssbits, Nov 17 '18 at 02:15

score 3 · Accepted Answer · answered Oct 16 '18 at 16:54

3

count skips null values. So you can count a case expression where the value is 0, and then use a having condition to check that this count is equal to the total count:

SELECT   ext_no, MAX(value)
FROM     test
GROUP BY ext_no
HAVING   COUNT(*) > 2 AND 
         COUNT(*) = COUNT(CASE value WHEN 0 THEN 1 END)

answered Oct 16 '18 at 16:54

Mureinik

297,002
52
306
350

thank you for your answer sir.. now i want to select all records.. i mean.. not distinct or group by..i want to select all that records having value 0 .. so that i can update or delete that records..how to modify this answer.. i tried using order by.. but it shows error.. – Sachin_Bhagat_Ssbits Nov 17 '18 at 02:08
@user10146017 if you have a new set of requirements, please post them as a new question – Mureinik Nov 17 '18 at 08:01
sure.. [here](https://stackoverflow.com/questions/53370339/mysql-query-to-delete-records-after-select) it is – Sachin_Bhagat_Ssbits Nov 19 '18 at 07:49

Shidersz · Answer 2 · 2018-10-16T16:58:00.260

0

You can try this one, using group by and the having clause:

SELECT
    ext_no,
    SUM(value) as value
FROM
    test
GROUP BY
    ext_no
HAVING
    count(*) > 2 AND SUM(value) = 0

edited Oct 16 '18 at 16:58

answered Oct 16 '18 at 16:51

Shidersz

16,846
2
23
48

1

This will return `ext_no`s that have zeros and omit the rows that have different values. The OP asked for "IF AND ONLY IF all that ext_no value is zero" – Mureinik Oct 16 '18 at 16:55

score 0 · Answer 3 · answered Oct 16 '18 at 17:01

0

Try this

SELECT t.ext_no, COUNT(*)
FROM test t
WHERE NOT EXISTS (
    SELECT 1
    FROM test
    WHERE ext_no = t.ext_no AND ext_no > 0
)
GROUP BY t.ext_no
HAVING COUNT(*) > 2

answered Oct 16 '18 at 17:01

Eric

3,165
1
19
25

Mysql query to select Distinct Records on conditions?

3 Answers3

Linked