How do I select rows from table that have one or more than one specific value in a column?-CodePudding

I have a table containing data such as:

BP_NUMBER,CONTRACT_TYPE
0000123, 1
0000123, 2
0000123, 3
0000123, 4
0000124, 4
0000124, 4
0000124, 4
0000125, 4
0000126, 1
0000126, 5

I want to select rows containing one or more occurrences of CONTRACT_TYPE = 4. In other words, I want to know who are the clients with one or more contracts of the same type and type 4.

I tried this query:

SELECT * FROM (
SELECT BP_NUMBER, CONTRACT_TYPE, COUNT(*) OVER (PARTITION BY BP_NUMBER) CT FROM CONTRACTS
WHERE (1=1)
AND DATE = '18/10/2022'
AND CONTRACT_TYPE = 4)
WHERE CT= 1;

But it returns rows with only one occurrence of CONTRACT_TYPE = 4.

Also tried something like:

SELECT BP_NUMBER FROM CONTRACTS 
WHERE (1=1)
AND CONTRACT_TYPE = 4 
AND CONTRACT_TYPE NOT IN (SELECT CONTRACT_TYPE FROM CONTRACTS WHERE CONTRACT_TYPE != 4 GROUP BY CONTRACT_TYPE);

Trying to avoid any other contract types than 4. I really don't understand why it doesn't work.

The expected result would be:

0000124   --(4 occurrences of type 4)
0000125   --(1 occurrence of type 4)

Any help? Thanks

CodePudding user response：

You can try something like this:

SELECT 
BP_NUMBER
FROM CONTRACTS c1
WHERE CONTRACT_TYPE = 4
AND NOT EXISTS 
(SELECT 1 FROM CONTRACTS c2 WHERE c2.BP_NUMBER = c1.BP_NUMBER
AND c2.CONTRACT_TYPE <> c1.CONTRACT_TYPE)

Depending on how you actually want to see it (and what other values you might want to include), you could either do a DISTINCT on the BP_NUMBER, or group on that column (and potentially others)

A similar result could also be achieved using an outer join between two instances of the CONTRACTS table. Essentially, you need the second instance of the same table so that you can exclude output rows when there are records with the "unwanted" contract types

CodePudding user response：

You can just do the aggregation like here:

WITH
    tbl AS
        (
            Select '0000123' "BP_NUMBER", '1' "CONTRACT_TYPE" From Dual Union All
            Select '0000123', '2'  From Dual Union All
            Select '0000123', '3'  From Dual Union All
            Select '0000123', '4'  From Dual Union All
            Select '0000124', '4'  From Dual Union All
            Select '0000124', '4'  From Dual Union All
            Select '0000124', '4'  From Dual Union All
            Select '0000125', '4'  From Dual Union All
            Select '0000126', '1'  From Dual Union All
            Select '0000126', '5'  From Dual 
        )
Select
    BP_NUMBER "BP_NUMBER",
    Count(*) "OCCURENCES"
From
    tbl
WHERE CONTRACT_TYPE = '4'
GROUP BY BP_NUMBER
ORDER BY BP_NUMBER
--  
--  R e s u l t :
--  
--  BP_NUMBER OCCURENCES
--  --------- ----------
--  0000123            1 
--  0000124            3 
--  0000125            1