Query to pull unique qty of an item-CodePudding

I have a table:

item	qty	area
1234	12	AAA
1234	11	BBB
1234	10	CCC
5678	5	AAA
4578	10	AAA
852	8	AAA
852	9	BBB
852	10	CCC

And the expected result to show only item available only in area AAA no other area

item	qty	area
5678	5	AAA
4578	10	AAA

I was trying to use the below query but the result was not sufficient as it give all the item in AAA not the unique ones I am looking for

select
  item
  , coalesce(sum(case when area = 'AAA' then qty end ) ,0) as 'AAA'
from MyTable
group by item

CodePudding user response：

Use NOT EXISTS to check that no other area exists for the same item.

select item, qty, area
from MyTable mt1
where area = 'AAA'
and not exists (
    select 1
    from MyTable mt2
    where mt2.item = mt1.item and mt2.Area <> 'AAA'
);

CodePudding user response：

Something like this perhaps. In the CASE expression, using "else 0" will avoid nulls and the warning message about them being eliminated.

WITH MyTable as (
    SELECT * 
    FROM (
        VALUES
            ('1234', 12, 'AAA'),
            ('1234', 11, 'BBB'),
            ('1234', 10, 'CCC'),
            ('5678', 5, 'AAA'),
            ('4578', 10, 'AAA'),
            ('852', 8, 'AAA'),
            ('852', 9, 'BBB'),
            ('852', 10, 'CCC')
        ) t (item, qty, area) 
)
SELECT item, coalesce(sum(case when area = 'AAA' then qty else 0 end ) ,0) as 'AAA'
from MyTable
group by item 
having count(*) = 1

[Edit] This also works, but I think Dale's answer is still better.

SELECT item, SUM(qty) as 'AAA'
from MyTable
group by item 
having count(*) = 1 AND MIN(area) = 'AAA'