CODEWITHSUNDEEP
sqlsql-servert-sql
losport
losport

Reputation: 19

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.

Upvotes: 1

Views: 3100

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

I would phrase this as:

Select t1.name, t1.amount 
from table1 t1
where (t1.amount = 0 and
       exists (select 1 from table1 tt1 where tt1.name = t1.name and tt1.amount > 0
      ) or
      (t1.amount > 0 and
       exists (select 1 from table1 tt1 where tt1.name = t1.name and tt1.amount = 0
      ) ;

This can take advantage of an index on table1(name, amount). And the beauty of the additional comparison is that only one exists clause needs to be evaluated for each row in the original table.

Upvotes: 1

Eric Brandt
Eric Brandt

Reputation: 8101

And since there are usually multiple ways to solve any given problem, here's another one.

The question you have is a pretty normal pattern. Select a data set that meets N conditions. In this case, there are just two, and both require that a certain type of row exists for given values. So another way to get there is to use the EXISTS clause.

It's logically very similar to Jason's method using IN clauses. Jason's answer, by the way, is deserving of being selected as THE answer. I'm just offering this up as an alternative approach to help you keep your options open.

An EXISTS clause uses a "correlated subquery", which means that a value from the outer query, table1 as t1, is used by the inner queries (inside the parenthesis, if that helps you picture it), usually in the WHERE clause of the inner query.

So below, each of the EXISTS clauses looks for any occurrence of the conditions in their WHERE clauses; the names match and the amounts meet their criteria. If any occurrence is found, the query returns a result set, which the EXISTS interprets as TRUE. If not, nothing comes back, which the EXISTS interprets as FALSE. Note that the results of the inner query don't matter at all, just that the WHERE clause is satisfied, or not. So I use SELECT 1 to show that the return value doesn't matter. You can put anything you want there, actually; even 1/0, and it'll work just fine.

select
  *
from 
  table1 as t1
where 
  exists (select 1 
          from table1 as t2 
          where t2.amount = 0
          and t2.Name = t1.Name)
  and
  exists (select 1
          from table1 as t3
          where t3.amount > 0
          and t3.Name = t1.Name);

See it in action here: Rextester demo

Upvotes: 1

GMB
GMB

Reputation: 222462

You can use window functions. Assuming that there are no negative values, you can do:

select name, amount
from (
    select
        t.*,
        min(amount) over(partition by name) min_amount,
        max(amount) over(partition by name) max_amount
    from mytable t
) t
where min_amount = 0 and max_amount > 0

If there are negative values:

select name, amount
from (
    select
        t.*,
        max(case when amount = 0 then 1 end) over(partition by name) has_zero_amount,
        max(amount) over(partition by name) max_amount
    from mytable t
) t
where has_zero_amount = 1 and max_amount > 0

I would expect that window functions would be more efficient that other solutions including several subqueries.

Upvotes: 1

Jason Goemaat
Jason Goemaat

Reputation: 29214

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.

Upvotes: 2

Related Questions