Find value that can be 0 or can be greater than 0 but must have at least one record equal 0

I need to find records that equal 0 and have other records that are greater than 0. The result should have at least one record that equals 0 and must also have at least one record that is greater than 0. It is easier to explain it with visuals:

Name amount
a1     0 
a1    100
a1    200
a2     0 
a2     0 
a2    200
a3    200
a3     0 
a3    100

It should not look like:

Name amount
a5    100 
a5    100
a5    200
a7     0 
a7     0 
a7     0
a6    200
a6    10 
a6    100

I have tried this:

Select name, amount 
from table1 
where amount = '0' AND amount > '0'

Sorry if this question is a bit ambiguous, it’s rather hard to explain.

Thanks in advance.

NB – Sorry if the question is not clear enough, wasn’t sure how to word it.

Answer

SELECT will give you rows from the table and the WHERE applies to those rowsto filter them. So your sample:

Select name, amount from table1 where amount = '0' AND amount > '0'

Will never return any rows because it only returns rows that have both amount = 0 and amount > 0 which is impossible. Also I hope those values are numeric, so you shouldn’t use the single quotes around them (i.e. ‘0’ should be plain 0)

GMB has a good way to do it with partition functions. The subquery reshapes the data into a new resultset that contains new columns ‘min_amount’ and ‘max_amount’ for all rows with the same id along with the other data for each row. You can then filter on those values, although you don’t mention if negative values could be present.

Another way to do it would be to add the checks to your filter criteria:

select name, amount
from table1 a
where a.id in (select id from table1 where amount = 0)
  and a.id in (select id from table1 where amount > 0)

This selects rows where id is in the list of ids with 0 as amount and the list of ids with amounts > 0.