SQL returns records where count has minor or zero records based on table-CodePudding

I'm trying to restore the number of contracts that have occurrences minors than specified in a table. For this, I have two tables, one where has stored contracts data and the hired_frequency and another, where has contract occurrences, that show how many times we has the contract. In this scenario I'm not able to create a SQL (in Postgres) that returns all the rows, that don't has occurrences and that where has occurrences minor that hired_frequency describes.

Table 1 - contracts

ID  | hired_frequency
527 | 1
528 | 3
526 | 5
555 | 1
413 | 3 -- not show this
500 | 1 -- not show this

Table 2 - occurrences

itinerary_fixed_id | contract_id
2443 | 527
2443 | 528
123  | 526
123  |555

My select (not working)

SELECT
    * 
FROM
    contracts 
WHERE
    hired_frequency > (
    SELECT COUNT
        ( * ) 
    FROM
        occurrences 
    WHERE
        contracts.ID = occurrences.contract_id 
    GROUP BY
    contract_id 
)

I've created a fiddle to test... http://sqlfiddle.com/#!17/95cd2/1/0

CodePudding user response：

When you joining on contracts.ID = occurrences.contract_id and group by it will aggregate and get you only 1 as a value , since there are no dups. What you need is just inner join

SELECT
    * 
FROM
    contracts 
WHERE
    hired_frequency > (
    SELECT COUNT
        ( * ) 
    FROM
        occurrences 
    WHERE
        contracts.ID = occurrences.contract_id 

    )

CodePudding user response：

Try this out

SELECT * 
FROM contracts a
WHERE ID NOT IN      ( SELECT b.contract_id 
                       FROM occurrences b )
OR hired_frequency > ( SELECT COUNT(c.contract_id) 
                       FROM occurrences c
                       WHERE a.ID = c.contract_id
                       GROUP BY c.contract_id);