CODEWITHSUNDEEP
sql
Wayne Earnshaw
Wayne Earnshaw

Reputation: 527

Oracle Join across tables to count 'outstanding invoices'

I'm trying to find "the number of outstanding invoices for a given contract rate".

SQL Fiddle here (updated with ujjwal's answer to add a distinct)

You'll notice I have 350 outstanding invoices which is a lot more than the 14 I expected so I guess I haven't restricted the INNER JOIN's correctly...

Any ideas would be great thanks (sorry I'm rusty on SQL), this is for the latest version of Oracle.

Wayne

Upvotes: 0

Views: 46

Answers (1)

Ujjwal
Ujjwal

Reputation: 3158

Can you try selecting only distinct entries:

 SELECT
        r.id AS rate_id,
        COUNT(distinct mi.id) AS outstanding_invoices           **change here**
    FROM
        contract_rate r
    INNER JOIN
        contract c ON r.contract_id = c.id
    INNER JOIN
        contractor con ON con.id = c.contractor_id
    INNER JOIN
        maintenance_item item ON item.contract_rate_id = r.id        
    INNER JOIN
        maintenance_invoice mi ON mi.contractor_id = c.contractor_id AND mi.status = 'Awaiting Approval'
    GROUP BY
        r.id

Upvotes: 1

Related Questions