<p>Ah ok I solved it. I was close. Just had to turn the inner join into a left join</p>
<pre><code>select sum (value) / 1000000 as current_supply from tx_out as tx_outer 
left join stake_address sa on tx_outer.stake_address_id = sa.id
where 
    not exists 
      ( select tx_out.id from tx_out 
       inner join tx_in on tx_out.tx_id = tx_in.tx_out_id and tx_out.index = tx_in.tx_out_index
          where tx_outer.id = tx_out.id and tx_in.tx_in_id &lt; 40491540
      ) 
and tx_outer.tx_id &lt;=40491540 
</code></pre>

CC BY-SA 4.0
2022-10-19T07:30:34.313Z
2022-10-19T07:30:34.313Z
Reddspark
<p>Your query excludes account balances (not sure why you're doing the join to stake_address here).</p>
<p>But if looking for total/circulating supply, you can already get those from dbsync table <code>ada_pots</code> (see example <a href="https://github.com/cardano-community/guild-operators/blob/alpha/files/grest/rpc/00_blockchain/totals.sql" rel="nofollow noreferrer">here</a> ).</p>
<p>The supply would only change per epoch - when reserved are unlocked.</p>

CC BY-SA 4.0
2022-10-21T04:53:49.967Z
Thanks - what do you mean by "excludes account balances"? And join to stake_address is because I wanted to (ultimately) analyze the largest stake_addresses. Sorry should have explained that up top or left out the join. Ada_Pots .. useful to know!
Reddspark
CC BY-SA 4.0
2022-10-21T06:40:37.743Z
I meant you're only looking up UTxOs, but there are rewards on accounts (that have been distributed from reserves), fundings extracted from treasury, deposits made for accounts, that you wouldnt see in tx_out table
RdLrT
CC BY-SA 4.0
2022-10-19T21:02:31.340Z
2022-10-19T21:02:31.340Z
RdLrT
<p>I'm trying to calculate the ADA balance at a prior point in time using DB-Sync.</p>
<p>What I did was first work out what the last tx.ID number of the 10th May 22 was, which in my version of the db is 40491540.</p>
<p>So then I wrote this query:</p>
<pre><code>select sum (value) / 1000000 as current_supply from tx_out as tx_outer 
inner join stake_address sa on tx_outer.stake_address_id = sa.id
where 
    not exists 
      ( select tx_out.id from tx_out 
       inner join tx_in on tx_out.tx_id = tx_in.tx_out_id and tx_out.index = tx_in.tx_out_index
          where tx_outer.id = tx_out.id and tx_in.tx_in_id &lt; 40491540
      ) 
and tx_outer.tx_id &lt;=40491540 
</code></pre>
<p>However this gives me a total circulating ADA balance of 26.bn which I don't this is right - should be more like 33.7bn.</p>
<p>What am I doing wrong?</p>

<cardano-db-sync>
How to calculate ADA balance at a prior point in time?
# How to calculate ADA balance at a prior point in time?

(Asked by: username_0 on 2022-10-18)

I'm trying to calculate the ADA balance at a prior point in time using DB-Sync.
What I did was first work out what the last tx.ID number of the 10th May 22 was, which in my version of the db is 40491540.
So then I wrote this query:
select sum (value) / 1000000 as current_supply from tx_out as tx_outer 
inner join stake_address sa on tx_outer.stake_address_id = sa.id
where 
    not exists 
      ( select tx_out.id from tx_out 
       inner join tx_in on tx_out.tx_id = tx_in.tx_out_id and tx_out.index = tx_in.tx_out_index
          where tx_outer.id = tx_out.id and tx_in.tx_in_id < 40491540
      ) 
and tx_outer.tx_id <=40491540 

However this gives me a total circulating ADA balance of 26.bn which I don't this is right - should be more like 33.7bn.
What am I doing wrong?

**Tags:** cardano-db-sync

---

## 2 answers

### Answer by username_0 on 2022-10-19. Score: 2

Ah ok I solved it. I was close. Just had to turn the inner join into a left join
select sum (value) / 1000000 as current_supply from tx_out as tx_outer 
left join stake_address sa on tx_outer.stake_address_id = sa.id
where 
    not exists 
      ( select tx_out.id from tx_out 
       inner join tx_in on tx_out.tx_id = tx_in.tx_out_id and tx_out.index = tx_in.tx_out_index
          where tx_outer.id = tx_out.id and tx_in.tx_in_id < 40491540
      ) 
and tx_outer.tx_id <=40491540


---

### Answer by username_1 on 2022-10-19. Score: 0

Your query excludes account balances (not sure why you're doing the join to stake\_address here).

But if looking for total/circulating supply, you can already get those from dbsync table `ada_pots` (see example [here](https://github.com/cardano-community/guild-operators/blob/alpha/files/grest/rpc/00_blockchain/totals.sql) ).

The supply would only change per epoch - when reserved are unlocked.

> **Comments:**
> **username_0**: Thanks - what do you mean by "excludes account balances"? And join to stake_address is because I wanted to (ultimately) analyze the largest stake_addresses. Sorry should have explained that up top or left out the join. Ada_Pots .. useful to know!  
> **username_1**: I meant you're only looking up UTxOs, but there are rewards on accounts (that have been distributed from reserves), fundings extracted from treasury, deposits made for accounts, that you wouldnt see in tx_out table  

---

