<p>The poor man's way to find out is to go to <a href="https://pool.pm" rel="nofollow noreferrer">pool.pm</a> and type in the pool of interest ticker, and at the top header is the number of delegators.</p>

CC BY-SA 4.0
2023-04-13T20:34:02.017Z
2023-04-13T20:34:02.017Z
TheStophe
<blockquote>
<p>It seems the the table delegation also contains the delegation history, not only the delegation at current time?</p>
</blockquote>
<p>Yes, that makes the <code>delegation</code> table a bit of a pain in the neck for something like this.</p>
<p>Its probably easier to use the <code>epoch_stake</code> table. The simplest query would be something like (choose the epoch you are interested in):</p>
<pre><code>select epoch_no, pool_id, count(*), sum (amount) as delegated from epoch_stake
    where epoch_no = 362 group by (pool_id, epoch_no) ; 

 epoch_no | pool_id | count |   delegated    
----------+---------+-------+----------------
      362 |    3081 |     2 |       70449031
      362 |     361 |    10 |   264344058414
      362 |    1286 |    34 |   709992195312
      362 |     560 |     1 |      507621786
      362 |    5214 |    10 |  4983514457611
      362 |    4090 |     3 |     1893354467
      362 |    4791 |     9 |   335768752617
      362 |    3159 |     3 |     5945300103
      362 |    5215 |    99 |  1358232546301
      362 |    4765 |     7 | 13999900770537
      362 |    2623 |    52 |  3429179709353
...
</code></pre>
<p>The pool fingerprint (ie <code>pool1rqgf6qd0...</code>) can be obtained with a join on the <code>pool_hash</code> table like you have in your example. Also, note that unfortunately, the <code>epoch_stake</code> table for epoch <code>N</code> is updated late in epoch <code>N - 1</code> (I think).</p>
<p>To get the delegation history for a pool, something like this should work:</p>
<pre><code>select epoch_no, pool_id, count(*), sum (amount) as delegated from epoch_stake
    where pool_id = 3081 group by (pool_id, epoch_no) order by epoch_no asc  ; 
 epoch_no | pool_id | count |   delegated    
----------+---------+-------+---------------
      258 |    3081 |     2 |      70643792
      259 |    3081 |     4 | 2105455092524
      260 |    3081 |     4 | 2105455092524
      261 |    3081 |     4 | 2105455092524
      262 |    3081 |     4 | 2105455092524
...
</code></pre>
<p>A simple join with the <code>pool_hash</code> table will allow you to use the pool fingerprint.</p>

CC BY-SA 4.0
2022-09-12T21:56:28.330Z
Thanks so much. You save my day.
cdt
CC BY-SA 4.0
2022-10-09T22:36:47.057Z
I would like to ask a follow-up question.
From this epoch_stake table, I can get the delegated amount for each stake_address per epoch.
Does this delegated amount already include the reward? 
Or I need to add the reward mount (from reward table) to get proper delegated amount?
cdt
CC BY-SA 4.0
2022-10-10T06:49:26.943Z
No, the reward for epoch `N` is not included in the delegated amount for that epoch because the rewards earned in epoch `N` are not distributed until epoch `N + 2`. From this I hope you can see that you should not add the reward amount to the delegated amount.
Erik de Castro Lopo
CC BY-SA 4.0
2022-10-11T01:04:58.677Z
I see. Thank you. So the delegated amount for epoch N (in epoch_stake table)  already includes rewards for epoch N-2? Or not include yet?
cdt
CC BY-SA 4.0
2022-10-11T02:42:42.237Z
Yes, the delegated amount for epoch `N` already includes rewards earned in epoch `N - 2` and earlier.
Erik de Castro Lopo
CC BY-SA 4.0
2022-10-11T14:34:37.943Z
Thanks very much
cdt
CC BY-SA 4.0
2022-09-12T00:01:29.967Z
2022-09-12T00:01:29.967Z
Erik de Castro Lopo
<p>Using cardano-db-sync, I want to get the number of delegators given a pool_id.</p>
<p>For example, this pool has 22 delegators.
<a href="https://cardanoscan.io/pool/18109d01af0c5c4495a64a9de061ad621156729afc699128c0ceee0e?tab=delegators" rel="nofollow noreferrer">https://cardanoscan.io/pool/18109d01af0c5c4495a64a9de061ad621156729afc699128c0ceee0e?tab=delegators</a></p>
<p>However, when I used the following query, the result did not match. The following query returns 66 unique values of stake_address.hash_raw.</p>
<pre><code>select pool_hash.view,stake_address.hash_raw::text, stake_address.view,  
delegation.active_epoch_no, delegation.slot_no
from delegation, pool_hash, stake_address
where delegation.pool_hash_id = pool_hash.id and delegation.addr_id =stake_address.id 
and pool_hash.view ='pool1rqgf6qd0p3wyf9dxf2w7qcddvgg4vu56l35ez2xqemhqun2gn7y'
</code></pre>
<p>I want to ask that</p>
<ol>
<li><p>It seems the the table <code>delegation</code> also contains the delegation history, not only the delegation at current time? If so, which table should I use to be able to filter out to get only the number of current delegators.</p>
</li>
<li><p>How can I get the number of delegators w.r.t a pool_id</p>
<p>(2.1) at the current epoch</p>
<p>(2.2) for each epoch</p>
</li>
</ol>

<stake-pool><cardano-db-sync>
How to get number of delegators of a pool?
# How to get number of delegators of a pool?

(Asked by: username_0 on 2022-09-11)

Using cardano-db-sync, I want to get the number of delegators given a pool_id.
For example, this pool has 22 delegators.
https://cardanoscan.io/pool/18109d01af0c5c4495a64a9de061ad621156729afc699128c0ceee0e?tab=delegators
However, when I used the following query, the result did not match. The following query returns 66 unique values of stake_address.hash_raw.
select pool_hash.view,stake_address.hash_raw::text, stake_address.view,  
delegation.active_epoch_no, delegation.slot_no
from delegation, pool_hash, stake_address
where delegation.pool_hash_id = pool_hash.id and delegation.addr_id =stake_address.id 
and pool_hash.view ='pool1rqgf6qd0p3wyf9dxf2w7qcddvgg4vu56l35ez2xqemhqun2gn7y'

I want to ask that

It seems the the table delegation also contains the delegation history, not only the delegation at current time? If so, which table should I use to be able to filter out to get only the number of current delegators.

How can I get the number of delegators w.r.t a pool_id
(2.1) at the current epoch
(2.2) for each epoch

**Tags:** stake-pool, cardano-db-sync

---

## 2 answers

### Answer by username_2 on 2022-09-12. Score: 4

It seems the the table delegation also contains the delegation history, not only the delegation at current time?

Yes, that makes the delegation table a bit of a pain in the neck for something like this.
Its probably easier to use the epoch_stake table. The simplest query would be something like (choose the epoch you are interested in):
select epoch_no, pool_id, count(*), sum (amount) as delegated from epoch_stake
    where epoch_no = 362 group by (pool_id, epoch_no) ; 

 epoch_no | pool_id | count |   delegated    
----------+---------+-------+----------------
      362 |    3081 |     2 |       70449031
      362 |     361 |    10 |   264344058414
      362 |    1286 |    34 |   709992195312
      362 |     560 |     1 |      507621786
      362 |    5214 |    10 |  4983514457611
      362 |    4090 |     3 |     1893354467
      362 |    4791 |     9 |   335768752617
      362 |    3159 |     3 |     5945300103
      362 |    5215 |    99 |  1358232546301
      362 |    4765 |     7 | 13999900770537
      362 |    2623 |    52 |  3429179709353
...

The pool fingerprint (ie pool1rqgf6qd0...) can be obtained with a join on the pool_hash table like you have in your example. Also, note that unfortunately, the epoch_stake table for epoch N is updated late in epoch N - 1 (I think).
To get the delegation history for a pool, something like this should work:
select epoch_no, pool_id, count(*), sum (amount) as delegated from epoch_stake
    where pool_id = 3081 group by (pool_id, epoch_no) order by epoch_no asc  ; 
 epoch_no | pool_id | count |   delegated    
----------+---------+-------+---------------
      258 |    3081 |     2 |      70643792
      259 |    3081 |     4 | 2105455092524
      260 |    3081 |     4 | 2105455092524
      261 |    3081 |     4 | 2105455092524
      262 |    3081 |     4 | 2105455092524
...

A simple join with the pool_hash table will allow you to use the pool fingerprint.

> **Comments:**
> **username_0**: Thanks so much. You save my day.  
> **username_0**: I would like to ask a follow-up question. From this epoch_stake table, I can get the delegated amount for each stake_address per epoch. Does this delegated amount already include the reward?  Or I need to add the reward mount (from reward table) to get proper delegated amount?  
> **username_2**: No, the reward for epoch `N` is not included in the delegated amount for that epoch because the rewards earned in epoch `N` are not distributed until epoch `N + 2`. From this I hope you can see that you should not add the reward amount to the delegated amount.  
> **username_0**: I see. Thank you. So the delegated amount for epoch N (in epoch_stake table)  already includes rewards for epoch N-2? Or not include yet?  
> **username_2**: Yes, the delegated amount for epoch `N` already includes rewards earned in epoch `N - 2` and earlier.  
> **username_0**: Thanks very much  

---

### Answer by username_1 on 2023-04-13. Score: 0

The poor man's way to find out is to go to [pool.pm](https://pool.pm) and type in the pool of interest ticker, and at the top header is the number of delegators.


---

