CODEWITHSUNDEEP
sqloracle-database
drew_is_good
drew_is_good

Reputation: 163

Exclude value of a record in a group if another is present v2

In the example table below, I'm trying to figure out a way to sum amount over marks in two situations: the first, when mark 'C' exists within a single id, and the second, when mark 'C' doesn't exist within an id (see id 1 or 2). In the first situation, I want to exclude the amount against mark 'A' within that id (see id 3 in the desired conversion table below). In the second situation, I want to perform no exclusion and take a simple sum of the amounts against the marks.

In other words, for id's containing both mark 'A' and 'C', I want to make the amount against 'A' as zero. For id's that do not contain mark 'C' but contain mark 'A', keep the original amount against mark 'A'.

My desired output is at the bottom. I've considered trying to partition over id or use the EXISTS command, but I'm having trouble conceptualizing the solution. If any of you could take a look and point me in the right direction, it would be greatly appreciated :)

example table:

id   mark  amount
------------------
1    A     1
2    A     3
2    B     2
3    A     1
3    C     3

desired conversion:

id   mark  amount
------------------
1    A     1
2    A     3
2    B     2
3    A     0
3    C     3

desired output:

mark    sum(amount)
--------------------
A       4
B       2
C       3

Upvotes: 1

Views: 161

Answers (2)

Andriy M
Andriy M

Reputation: 77667

You could slightly modify my previous answer and end up with this:

SELECT
  mark,
  sum(amount) AS sum_amount
FROM atable t
WHERE mark <> 'A'
  OR NOT EXISTS (
    SELECT *
    FROM atable
    WHERE id = t.id
      AND mark = 'C'
  )
GROUP BY
  mark
;

There's a live demo at SQL Fiddle.

Upvotes: 1

Bacon Bits
Bacon Bits

Reputation: 32170

Try:

select
    mark,
    sum(amount)
from ( select
        id,
        mark,
        case 
            when (mark = 'A' and id in (select id from table where mark = 'C')) then 0
            else amount
        end as amount
    from table ) t1
group by mark

Upvotes: 0

Related Questions