Using a column in sql join without adding it to group by clause

Question

My actual table structures are much more complex but following are two simplified table definitions:

Table invoice

CREATE TABLE invoice (
  id integer NOT NULL,
  create_datetime timestamp with time zone NOT NULL,
  total numeric(22,10) NOT NULL
);

id   create_datetime   total    
----------------------------
100  2014-05-08        1000

Table payment_invoice

CREATE TABLE payment_invoice (
  invoice_id integer,
  amount numeric(22,10)
);

invoice_id  amount
-------------------
100         100
100         200
100         150

I want to select the data by joining above 2 tables and selected data should look like:-

month      total_invoice_count  outstanding_balance
05/2014    1                    550

The query I am using:

select
to_char(date_trunc('month', i.create_datetime), 'MM/YYYY') as month,
count(i.id) as total_invoice_count,
(sum(i.total) - sum(pi.amount)) as outstanding_balance
from invoice i
join payment_invoice pi on i.id=pi.invoice_id
group by date_trunc('month', i.create_datetime)
order by date_trunc('month', i.create_datetime);

Above query is giving me incorrect results as sum(i.total) - sum(pi.amount) returns (1000 + 1000 + 1000) - (100 + 200 + 150) = 2550.
I want it to return (1000) - (100 + 200 + 150) = 550

And I cannot change it to i.total - sum(pi.amount), because then I am forced to add i.total column to group by clause and that I don't want to do.

Community · Accepted Answer · 2017-05-23 12:23:36Z

2

You need a single row per invoice, so aggregate payment_invoice first - best before you join.
When the whole table is selected, it's typically fastest to aggregate first and join later:

SELECT to_char(date_trunc('month', i.create_datetime), 'MM/YYYY') AS month
     , count(*)                                   AS total_invoice_count
     , (sum(i.total) - COALESCE(sum(pi.paid), 0)) AS outstanding_balance
FROM   invoice i
LEFT   JOIN  (
    SELECT invoice_id AS id, sum(amount) AS paid
    FROM   payment_invoice pi
    GROUP  BY 1
    ) pi USING (id)
GROUP  BY date_trunc('month', i.create_datetime)
ORDER  BY date_trunc('month', i.create_datetime);

LEFT JOIN is essential here. You do not want to loose invoices that have no corresponding rows in payment_invoice (yet), which would happen with a plain JOIN.

Accordingly, use COALESCE() for the sum of payments, which might be NULL.

SQL Fiddle with improved test case.

edited May 23, 2017 at 12:23

CommunityBot

11 silver badge

answered May 9, 2014 at 19:28

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ivish Over a year ago

Thanks a lot Erwin for your reply. In fact I do not have enough words to say thank you :-) I was banging my head against the wall since afternoon. Your solution worked like a magic wand. Thank you!!!

ivish Over a year ago

Hello Erwin, I have one quick follow up question. In your feedback, I can see, we are not mentioning the join needs to be performed on i.id=pi.invoice_id. Just wanted to know does 'pi USING (id)' takes care of that part. Thank you!

Erwin Brandstetter Over a year ago

@user238990: As you can see in the fiddle it does. The reason is the column alias in the subquery: invoice_id AS id.

Torino · Accepted Answer · 2014-05-09 19:19:46Z

1

Do the aggregation in two steps. First aggregate to a single line per invoice, then to a single line per month:

select
  to_char(date_trunc('month', t.create_datetime), 'MM/YYYY') as month,
  count(*) as total_invoice_count,
  (sum(t.total) - sum(t.amount)) as outstanding_balance
from (
    select i.create_datetime, i.total, sum(pi.amount) amount
    from invoice i
    join payment_invoice pi on i.id=pi.invoice_id
    group by i.id, i.total
) t
group by date_trunc('month', t.create_datetime)
order by date_trunc('month', t.create_datetime);

answered May 9, 2014 at 19:19

Torino

5882 silver badges9 bronze badges

1 Comment

ivish Over a year ago

Hello Torino, I really appreciate your reply. But since Erwin's reply also takes care of the 'no payments for an invoice' scenario I am accepting his feedback as the final answer.

Tom · Accepted Answer · 2014-05-09 19:24:54Z

1

See sqlFiddle

SELECT TO_CHAR(invoice.create_datetime, 'MM/YYYY') as month,
       COUNT(invoice.create_datetime) as total_invoice_count,
       invoice.total - payments.sum_amount as outstanding_balance
FROM invoice
JOIN 
(
    SELECT invoice_id, SUM(amount) AS sum_amount
    FROM payment_invoice
    GROUP BY invoice_id
) payments
ON invoice.id = payments.invoice_id
GROUP BY TO_CHAR(invoice.create_datetime, 'MM/YYYY'), 
         invoice.total - payments.sum_amount

edited May 9, 2014 at 19:24

answered May 9, 2014 at 19:16

Tom

7,7401 gold badge26 silver badges49 bronze badges

1 Comment

ivish Over a year ago

Thank you Tom for taking out time to answer my query. Since Erwin's reply also takes care of the 'no payments for an invoice' scenario I am accepting his feedback as an answer.

Collectives™ on Stack Overflow

Using a column in sql join without adding it to group by clause

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related