CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.4
Michael Nielsen
Michael Nielsen

Reputation: 1242

SQL SUM and GROUP BY based on WHERE clause

I'm running PostgreSQL 9.4 and have the following table structure for invoicing:

id BIGINT, time UNIX_TIMESTAMP, customer TEXT, amount BIGINT, status TEXT, billing_id TEXT

I hope I can explain my challenge correctly.

A invoice record can have 2 different status; begin, ongoing and done. Several invoice records can be part of the same invoice line, over time.

So when an invoice period begins, a record is started with status begin. Then every 6 hour there will be generated a new record with status ongoing containing the current amount spend in amount. When an invoice is closed a record with status done is generated with the total amount spend in column amount. All the invoice records within the same invoice contains the same billing_id.

To calcuate a customers current spendings I can run the following:

SELECT sum(amount) FROM invoice_records where id = $1 and time between '2017-06-01' and '2017-07-01' and status = 'done'

But that does not take into account if there's an ongoing invoice which are not closed yet.

How can I also count the largest billing_id with no status done?

Hope it make sense.

Upvotes: 0

Views: 579

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

Per invoice (i.e. billing_id) you want the amount of the record with status = 'done' if such exists or of the last record with status = 'ongoing'. You can use PostgreSQL's DISTINCT ON for this (or use standard SQL's ROW_NUMBER to rank the records per invoice).

SELECT DISTINCT ON (billing_id) billing_id, amount
FROM invoice_records 
WHERE status IN ('done', 'ongoing', 'begin')
ORDER BY 
  billing_id, 
  CASE status WHEN 'done' THEN 1 WHEN 'ongoing' THEN 2 ELSE 3 END,
  unix_timestamp desc;

The ORDER BY clause represents the ranking.

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

select sum (amount), id
from (
    select distinct on (billing_id) *
    from (
        select distinct on (status, billing_id) *
        from invoice_records
        where
            id = $1
            and time between '2017-06-01' and '2017-07-01'
            and status in ('done', 'ongoing')
        order by status, billing_id desc
    ) s
    order by billing_id desc
) s

Upvotes: 0

Related Questions