0

Consider this:

SELECT
  review.clicker_id,
  sum(review.done - review.due) as timespent,
  PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY timespent ASC) as median
FROM review WHERE monologue_id=7142 GROUP BY clicker_id ORDER BY timespent ASC;

I'm trying to get the median value for sum(review.done - review.due) (a time interval.) But, obviously, I'm not allowed to do it this way:

Error in query: ERROR: column "timespent" does not exist LINE 4:
PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY time ASC) as m...

How am I supposed to do this?

Fabien Snauwaert
  • 4,995
  • 5
  • 52
  • 70
  • 1
    Please see https://stackoverflow.com/questions/11785622/how-to-use-an-alias-in-a-postgresql-order-by-clause. Also, calling a column `time` is probably not a great idea as it's a data type. – w08r Jan 17 '20 at 11:44
  • 1
    Does this answer your question? [How to use an ALIAS in a PostgreSQL ORDER BY clause?](https://stackoverflow.com/questions/11785622/how-to-use-an-alias-in-a-postgresql-order-by-clause) – w08r Jan 17 '20 at 11:45
  • 2
    Renamed `time` to `timespent` to avoid confusion. But this is not the issue here. – Fabien Snauwaert Jan 17 '20 at 11:48
  • No, the issue is ordering by an alias, answered in the linked question, but the rename is good. – w08r Jan 17 '20 at 11:59
  • @FabienSnauwaert could you also add a few data samples and the exact expected result? – Jim Jones Jan 17 '20 at 12:11
  • maybe this? `SELECT review.clicker_id, sum(review.done - review.due) as timespent, PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY sum(review.done - review.due) ASC) as median FROM review WHERE monologue_id=7142 GROUP BY clicker_id ORDER BY timespent ASC;` – Jim Jones Jan 17 '20 at 12:13
  • @wobr Sorry, but I still don't see how this answers the question. (Alias in the outermost query works fine as it is; replacing it with `GROUP BY 1 ORDER BY 2 ASC` would work the same but is not needed.) Now, if I try to replace the alias inside of `GROUP(ORDER BY timespent ASC)` with the column number (2), all I get for that column is a bunch of 2s, not the expected median. – Fabien Snauwaert Jan 17 '20 at 12:17
  • @JimJones results in "ERROR: aggregate function calls cannot be nested". Will edit to add details, thanks. – Fabien Snauwaert Jan 17 '20 at 12:18
  • @FabienSnauwaert I see.. hard to test without data :D Maybe a CTE? `WITH j AS ( SELECT review.clicker_id, sum(review.done - review.due) as timespent FROM review WHERE monologue_id=7142 ) SELECT clicker_id,timespent, PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY timespent ASC) as median FROM j WHERE GROUP BY clicker_id ORDER BY timespent ASC` – Jim Jones Jan 17 '20 at 12:22
  • Had a play with some dummy data, is this what you want: `WITH totals AS (SELECT clicker_id,review.done - review.due AS timespent FROM review WHERE monologue_id = 7142) SELECT clicker_id,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY timespent ASC) AS median FROM totals GROUP BY 1;` – w08r Jan 17 '20 at 13:50

0 Answers0