How to use Distinct On with COALESCE-CodePudding

I'm trying to build a query which sums up a column for all rows with unique IDs (interested only in the latest entry for a given ID). The thing is the IDs are coming from one of 2 columns so I'm using COALESCE. This is what I roughly want:

SELECT COALESCE(SUM(ct.amount), 0)
FROM (
  SELECT
    DISTINCT ON (COALESCE(NULLIF(pt.originalID, 0), pt.ID)) id,  -- use originalID otherwise ID
    pt.amount
  FROM prettyTable AS pt
  WHERE ...
  ORDER BY pt.creation_time DESC
) AS ct
...

So I want the most recently created amount for each unique id where the id comes from either originalID or if that's 0 then simply ID.

Note: I've tried adding the creation_time to the select clause but that doesn't work. I've also tried matching the ORDER BY with the SELECT but I can't make that work either...

Example Rows:

originalID, ID, creation_time, amount, ...
 0        ,  1, 2004-10-19   , 10 -- add to sum

 0        ,  2, 2004-10-20   , 10 -- coalesced id=2; ignore 
 2        ,  3, 2004-10-21   , 20 -- coalesced id=2; ignore
 2        ,  4, 2004-10-22   , 30 -- coalesced id=2; add to sum

 0        ,  5, 2004-10-23   , 10 -- coalesced id=5; add to sum
 5        ,  6, 2004-10-19   , 60 -- coalesced id=5; ignored

The result for the above should be the sum of the 1st, 4rd and 5th rows = 10 30 10 = 50

CodePudding user response：

Your ORDER BY clause requires the column you applied DISTINCT ON on in its definition, hence your problem may be solved like this:

SELECT COALESCE(SUM(ct.amount), 0)
FROM (SELECT DISTINCT ON 
          (COALESCE(NULLIF(pt.originalID, 0), pt.ID)) id,     
          pt.amount
      FROM prettyTable AS pt
      ORDER BY (COALESCE(NULLIF(pt.originalID, 0), pt.ID)),
               pt.creation_time DESC
) AS ct

Check the demo here.