CODEWITHSUNDEEP
sqlsql-serversubqueryexists
user3656231
user3656231

Reputation: 453

Filter records in a table based on some columns

I have one table (say , table name is "items") as given below.

id timestamp name amount
100 16106800 pqr 10
100 16106800 pqr 0
100 16106400 pqr 15

If there are more than one record with same name and same timestamp then I want to exclude one with zero amount. For example - In the above table , we have two records (first and second records) with same name and same timestamp, so I have to exclude one record with zero amount.

Expected result is as below:

id timestamp name amount
100 16106800 pqr 10
100 16106400 pqr 15

I wrote a query like below but that doesn't work completely.

SELECT *
FROM items w
WHERE EXISTS (
    SELECT *
    FROM items x  
    WHERE x.timestamp = w.timestamp AND x.name=w.name
) AND w.amount > 0

My above query getting failed when all the timestamps are different. If there is no records with same name and same timestamp then it will not exclude anything from items table.

EDIT : If there is only one row with zero amount in that table then it should not be excluded.

Upvotes: 0

Views: 388

Answers (5)

Joe Taras
Joe Taras

Reputation: 15389

My query produces these results:

If you have only one record despite the amount, it is showed

If you have more than one record, it will show only the records with amount > 0

So, try this:

SELECT *
FROM items t1
WHERE NOT EXISTS 
    (SELECT 1 FROM items t2
    WHERE t1.id = t2.id 
    AND t1.timestamp = t2.timestamp 
    AND t1.name = t2.name
    AND t2.amount > 0)
OR t1.amount > 0

See SQL Fiddle

Be aware: I've written on SQL Fiddle with a different DBMS but the SQL used is standard, so it's OK for SQL Server too.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270693

Why not just aggregate?

select id, timestamp, name, sum(amount) as amount
from items
group by id, timestamp, name;

This returns one row per id/timestamp/name combination, with the total amount for that combination. I suspect this does what you really want.

But, you can also modify your query by tweaking the logic:

SELECT i.*
FROM items i
WHERE i.amount > 0 OR
      NOT EXISTS (SELECT 1
                  FROM items i2
                  WHERE i2.timestamp = i.timestamp AND
                        i2.name = i.name AND
                        i2.amount > 0
                 );

That is, return all rows where the amount > 0. And then also return all rows for a timestamp/name combination where there is no row with an amount > 0.

Upvotes: 1

Charlieface
Charlieface

Reputation: 72153

A conditional Window Aggregate COUNT(CASE...) OVER... is probably your best bet:

SELECT id, timestamp, name, amount
FROM (
    SELECT *,
        count(case when amount > 0 then 1 end)
            over(partition by timestamp, name) CountNonZeroes
    FROM Table
) t
WHERE CountNonZeroes = 0 OR amount > 0

Upvotes: 0

Serg
Serg

Reputation: 22811

Alternatively you can use count() over()

SELECT id, timestamp, name, amount
FROM (
    SELECT *, count(*) over(partition by id, timestamp, name) cnt
    FROM yourtable) t
WHERE cnt = 1 OR amount > 0

db<>Fiddle

Upvotes: 0

Karan Gandhi
Karan Gandhi

Reputation: 1494

;with a as (
select distinct id,timestamp,name from items
),
with b as (
select * from items
where amount > 0
)
select a.id,a.timestamp,a.name,isnull(b.amount,0) amount  
from a join b on a.timestamp = b.timestamp and a.name = b.name

Upvotes: 0

Related Questions