Issue with a conditional sub query I have the following table:

value    tag    main_tag
------------------------
    1    tag_A  ABC
   -1    tag_C  ABC
   -1    tag_A  BCD
    1    tag_C  BCD
   -1    tag_A  ABC
    1    tag_C  ABC
   -1    tag_A  BCD
    1    tag_C  BCD
   -1    tag_C  BCD


As demonstrated, there are three columns value, tag, main_tag. Under each main_tag there are several other tag s. I want to get the rows where sum of value s of each tag is equal to zero under each main_tag.

This is the query I tried,

SELECT * 
FROM foo 
WHERE ( WHERE sum(value) = 0 GROUP BY tag) 
GROUP BY main_tag ;


First I am trying to find the sum of values GROUP BY tag and then I am using GROUP BY main_tag. But it is not working.

Output Should be :

value    tag    main_tag
------------------------
    1    tag_A  ABC 
   -1    tag_C  ABC 
   -1    tag_A  ABC 
    1    tag_C  ABC
Not sure that I got it right, but:

select tag, main_tag
from foo 
group by tag, main_tag
having sum(value) = 0;


retrieves tag, main_tag where sum(value) is 0. You can join this result with the original table as:

select f.* 
from foo f
join ( 
    select tag, main_tag
    from foo 
    group by tag, main_tag
    having sum(value) = 0
) as t
    on t.tag = f.tag
   and t.main_tag = f.main_tag;