Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Display only the record with zero value SQL query

Input table has following data:

            +--------+-----+
            |   Id   | set |
            +--------+-----+
            | ABC123 |   1 |
            | ABC123 |   0 |
            | ABC123 |   1 |
            | XYZ123 |   0 |
            | XYZ123 |   0 |
            | XYZ123 |   0 |
            | ZAC123 |   1 |
            | ZAC123 |   0 |
            | TNB123 |   0 |
            | TNB123 |   0 |
            | TNB123 |   0 |
            | BBB123 |   1 |
            | BBB123 |   0 |
            | BBB123 |   1 |
            | BBB123 |   1 |
            +--------+-----+

Need result as below:

            +--------+-----+
            |   Id   | set |
            +--------+-----+
            | XYZ123 |   0 |
            | XYZ123 |   0 |
            | XYZ123 |   0 |
            | TNB123 |   0 |
            | TNB123 |   0 |
            | TNB123 |   0 |
            +--------+-----+

Query

Select * 
from X
where set='0'
having count(*)>1

Not working as some are only one record as well

Tags:

5

Answers


  1. 0 
    select t.id
from your_table as t
group by t.id
having  min(t.set)=0 and max(t.set)=0
    Login or Signup to reply.
  2. 0

    This is one way:

    select *
from my_table
where id not in (select id from my_table where my_table.set <> 0)

    This will select all ids where set is not 0 in a subquery, then filter out those rows with a not in over the whole table.

    https://dbfiddle.uk/QCPgxcK7 (Thanks @xQbert for the setup code)

    Login or Signup to reply.
  3. 0

    sergey was close:

    The issue here is you need to return all records and a group by given duplicate values will eliminate those. So we need to first get a set of data which is for records where the set is all 0’s and has a count > 1 then query the base set (x) for those records; returning all of them.

    DEMO

    WITH CTE as (select z.id
FROM x as z
GROUP BYz.id
HAVING min(z.set)=0 and max(z.set)=0 and count(*) > 1)

SELECT * 
FROM x 
WHERE ID in (SELECT ID 
             FROM CTE)

    May not need the having count(*)>1 if you want records id’s which only have 1 record with a set of 0.

    Giving us:

    +--------+-----+
|   ID   | set |
+--------+-----+
| XYZ123 |   0 |
| XYZ123 |   0 |
| XYZ123 |   0 |
| TNB123 |   0 |
| TNB123 |   0 |
| TNB123 |   0 |
+--------+-----+
    Login or Signup to reply.
  4. 0

    Use SUM() window function to sum all set values for each Id. Return the Id’s where that sum is equal to 0.

    select id, `set`
from
(
  select *, sum(`set`) over (partition by id) set_sum
  from x
) dt
where set_sum = 0

    Demo: https://dbfiddle.uk/iIw7_VHz (Built on @Dogbert’s, thanks!)

    Note: If set can be negative, do

    sum(abs(`set`))
    Login or Signup to reply.
  5. 0

    Two other ways :

    SELECT *
FROM x x1
WHERE NOT EXISTS (
  SELECT *
  FROM x x2
  WHERE x1.ID = x2.ID
  AND x2.set = 1
)

SELECT *
FROM x
WHERE ID IN (
  SELECT ID
  FROM x
  GROUP BY x.ID
  HAVING BIT_OR(x.set) = 0
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search