CODEWITHSUNDEEP
sqlpostgresqlgroup-by
Yassine
Yassine

Reputation: 183

Why doesn't this code return the correct result?

Table Propositions
Table Invoices (proposition_id, total_left)

The goal is to return all propositions that have all their invoices's total_left = 0.

Example:

Proposition 1:
 Invoice 1, total_left: 0
 Invoice 2, total_left: 10

Proposition 2:
 Invoice 3, total_left: 0
 Invoice 4, total_left: 0

So in this example, the Proposition 2 should be returned, and not 1.

Here is the actual SQL.

SELECT
    "propositions".id
FROM
    "propositions"
    INNER JOIN "invoices" ON "invoices"."proposition_id" = "propositions"."id"
GROUP BY
    invoices.total_left,
    propositions.id
HAVING
    sum(invoices.total_left) = 0.0

But it doesn't work, it still returns Proposition Ids that have invoices with total_left > 0.

Upvotes: 1

Views: 41

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

For what you want to do, the join is almost certainly unnecessary:

SELECT i.proposition_id
FROM invoices i
GROUP BY i.proposition_id
HAVING SUM(i.total_left) = 0.0;

You would only need the JOIN if you had proposition_id values in invoices that are not in propositions. That seems highly unlikely in a well-formed data model.

Upvotes: 1

Sebastian Brosch
Sebastian Brosch

Reputation: 43574

You have to remove the invoices.total_left from GROUP BY:

SELECT
  propositions.id
FROM
  propositions
  INNER JOIN invoices ON invoices.proposition_id = propositions.id
GROUP BY
  propositions.id
HAVING
  SUM(invoices.total_left) = 0.0

demo on dbfiddle.uk

You get to much groups by using invoices.total_left on GROUP BY. You also don't get the expected SUM of each propositions.id because you have multiple records with the same propositions.id (but different invoices.total_left).

Since you want to know the SUM of invoices.total_left of each propositions.id you only need to GROUP BY the propositions.id column.

Upvotes: 3

Related Questions