Select the rows only when all sub columns contain the same value [duplicate]

Question

Suppose I have 3 columns, Item, SubItem, Number.

I want to select the rows only when all the subitems have number as 0.

For example in the below table, only Phone and Pant should be selected since all their subitems have number=0.

Also duplicate rows should not be returned. Only one row of each item should be returned.

Answer 1

Note: Please also tag which engine and version of MySQL (and you can remove the SQL Server tag if you're not referring to Microsoft SQL Server, which is what that tag is for). You should also include more information such as your Table's definition (preferably through DDL statements).

But assuming your Number column is a numerical data type, then you can GROUP BY the Item column and SUM() the Number column in a subquery to achieve what you want like so:

SELECT Item, subItem, Number
FROM Table1
WHERE Item IN
(
    SELECT Item
    FROM Table1
    GROUP BY Item
    HAVING SUM(Number) = 0
) 

You can also re-write the above in a more relational manner, which might yield a more efficient query plan, using an INNER JOIN instead, like so:

SELECT T1.Item, T1.subItem, T1.Number
FROM Table1 T1
INNER JOIN
(
    SELECT Item
    FROM Table1
    GROUP BY Item
    HAVING SUM(Number) = 0
) S
    ON T1.Item = S.Item

Answer 2

SELECT DISTINCT Item
FROM table t1
WHERE NOT EXISTS ( SELECT NULL
                   FROM table t2
                   WHERE t1.Item = t2.Item
                     AND t2.Number <> 0 );

All another column returning makes no sense.

Answer 3

I agree with Akina - subtype doesn't seem to have any logic behind which value is selected, if you exclude subtype and use type and number you can write your query as follows using the criteria that all rows returned by the correlated subquery have number equal to 0, where the correlation is by item. see below

create table #table1 (item varchar(10), subitem varchar(10), number int)
insert #table1 values('Shirt','XL', 3),('Shirt','L', 0),('Shirt','M', 0),('Phone','64Gb',0) ,('Phone','32Gb',0),
('Shirt','S', 0), ('Pant','XL', 0),('Pant','L', 0),('Pant','M', 0), ('Pant','S', 0)

select * from #table1


SELECT distinct
    t1.Item,
    t1.Number
FROM #Table1 t1
WHERE 0 = ALL
(
    SELECT t2.Number
    FROM #Table1 t2
    WHERE t1.Item  = t2.Item
)

Answer 4

(MySQL answer)

This generalizes the problem to say "all numbers are the same" (not necessarily 0).

SELECT item,
       GROUP_CONCAT(subitem),
       MIN(number)
    FROM Table1
    GROUP BY item
    HAVING MIN(number) = MAX(number)

It is improper to select "subitem"; you need to specify which one you desire. I chose to get a commalist of all of them. MIN(subitem) would be another possibility. Perhaps ANY(subitem) would work (depending on Version).

cf ONLY_FULL_GROUP_BY.