TotalCount based on column that still needs to be fetched

Question

Given the fetched row below I need to also total how may id-token pair has a valuableField. The value in valuableField may have duplicates

the field

id - column of table

token - column of table

valuableField - column of table

How do I do the totalCountOfValuableFIeld field?

totalCountOfValuableField - not a column in a table and is the total count of distinct `valuableField per id-token pair.

I tried this query but it forces me to group the rows but that is not what I want.

select id, token, valuableField, count(distinct valuableField) from table_1

Sample Data

Current Table

╔════════╦═════════════╦═══════════════╗
║ id     ║ token       ║ valuableField ║ 
╠════════╬═════════════╬═══════════════╣
║     88 ║ test        ║ unique1       ║
║     88 ║ test        ║ duplicate1    ║
║     88 ║ random1     ║ 1unique       ║
║     88 ║ test        ║ duplicate1    ║
║     76 ║ bar         ║ 1unique       ║
║     76 ║ bar         ║ 2unique       ║
╚════════╩═════════════╩═══════════════╝

What I want

╔════════╦═════════════╦═══════════════╦════════════════════════════╗
║ id     ║ token       ║ valuableField ║ totalCountOfValuableField  ║ 
╠════════╬═════════════╬═══════════════╬════════════════════════════╣
║     88 ║ test        ║ unique1       ║            2               ║
║     88 ║ test        ║ duplicate1    ║            2               ║
║     88 ║ random1     ║ 1unique       ║            1               ║
║     88 ║ test        ║ duplicate1    ║            2               ║
║     76 ║ bar         ║ 2unique       ║            2               ║
║     76 ║ bar         ║ 3unique       ║            2               ║
╚════════╩═════════════╩═══════════════╩════════════════════════════╝

On a sidenote: You should not call a column `id` when it's not the table's ID. An ID is supposed to uniquely identify a record in the table. Yours does not. — Thorsten Kettner, Feb 23 '18 at 08:19
Why does the row with `unique1` have a count of `2`? The value `unique1` only appears once in your sample data — , Feb 23 '18 at 08:28
**88-test** pair has two unique `valuableField` (unique1 and duplicate1) — gzz, Feb 23 '18 at 08:33

score 2 · Answer 1 · 2018-02-23T08:39:16.170

Unfortunately Postgres does not (yet) support distinct in a window function. Based on the workaround for SQL Server, you can do the following:

select id, 
       token, 
       valuableField, 
       count(*) filter (where rn = 1) over (partition by id, token) 
from (
  select id, token, valuableField, 
         row_number() over (partition by id, token, valuableField) as rn
  from table_1
) t

Thorsten Kettner · Accepted Answer · 2018-02-23T08:40:12.640

1

From your expected results it seems you want to count distinct valuableField per id and token. ~~You'd do this with COUNT DISTINCT OVER:~~

select
  id,
  token,
  valuableField,
  count(distinct valuableField) over (partition by id, token) as total
from mytable;

UPDATE: As has been mentioned, PostgreSQL doesn't support DISTINCT in window functions. So you must use a subquery instead:

select
  id,
  token,
  valuableField,
  (
    select count(distinct m2.valuableField)
    from mytable m2
    where m2.id = m1.id and m2.token = m1.token
  ) as total 
from mytable m1;

This, however is not "how may id-token pair has a valuableField". If you wanted that, you'd have to partition by valuableField and count the distinct id/token pairs.

edited Feb 23 '18 at 08:40

answered Feb 23 '18 at 08:14

Thorsten Kettner

89,309
7
49
73

Unfortunately Postgres does not allow `distinct` in a window function – Feb 23 '18 at 08:24
@a_horse_with_no_name: Oh, how disappointing. I knew that SQL Server doesn't, but then that's just SQL Server. But PostgreSQL? They are usually so good. – Thorsten Kettner Feb 23 '18 at 08:32
I agree, that is disappointing. If I recall correctly there will be some enhancements to window functions for Postgres 11 - but I don't know if that includes the support for `distinct` – Feb 23 '18 at 08:46
thanks you guys. I've accepted this one as this came in first than @horse answer, but upvoted you two as your answers help me. Did this sub query approach and works. Just to add, if token is nullable, one should use `is not distinct from`. – gzz Feb 23 '18 at 11:57

TotalCount based on column that still needs to be fetched

2 Answers2