<p>Here is what I have found returns the assets, and their respective quantities, for a given stake address. Enjoy!</p>
<p><code>SELECT encode(ma.policy::bytea, 'hex') as policy, ma.fingerprint, SUM(matxo.quantity) AS total_quantity FROM tx_out AS txo LEFT JOIN tx_in AS txi ON txo.tx_id = txi.tx_out_id AND txo.index::smallint = txi.tx_out_index::smallint  LEFT JOIN tx ON tx.id = txo.tx_id LEFT JOIN block ON tx.block_id = block.id  LEFT JOIN stake_address AS sa ON txo.stake_address_id = sa.id  LEFT JOIN ma_tx_out AS matxo ON matxo.tx_out_id = txo.id  LEFT JOIN multi_asset AS ma ON ma.id = matxo.ident  WHERE sa.view = '&lt;stake_address_here&gt;'  AND txi.tx_in_id IS NULL  AND block.epoch_no IS NOT NULL  AND ma.policy IS NOT NULL  group by ma.fingerprint, ma.policy  order by total_quantity desc;</code></p>

CC BY-SA 4.0
2024-02-01T23:00:17.147Z
2024-02-01T23:00:17.147Z
RelevantData
<p>I am trying to develop the PostgreSQL query that will return all the assets of a given stake address and their respective quantities.</p>
<p>What needs to be joined and queried in order to determine what's in a given wallet?</p>
<p>If I query for the tx_out's of a given stake address:</p>
<p><code>select * from tx_out where stake_address_id = X order by tx_id desc;</code></p>
<p>EDIT:
Here is what I have so far, with summed quantities for grouped fingerprints and not yet with sum of ADA added to result, just the assets...</p>
<p>EDIT 2: With policy added, ordered by quantity of asset. I think is what I wanted, just does not have quantity of ADA row in results yet.</p>
<pre><code>SELECT encode(ma.policy::bytea, 'hex') as policy, ma.fingerprint, SUM(matxo.quantity) AS total_quantity
FROM tx_out AS txo
LEFT JOIN tx_in AS txi ON txo.tx_id = txi.tx_out_id AND txo.index::smallint = txi.tx_out_index::smallint 
LEFT JOIN tx ON tx.id = txo.tx_id
LEFT JOIN block ON tx.block_id = block.id 
LEFT JOIN stake_address AS sa ON txo.stake_address_id = sa.id 
LEFT JOIN ma_tx_out AS matxo ON matxo.tx_out_id = txo.id 
LEFT JOIN multi_asset AS ma ON ma.id = matxo.ident 
WHERE sa.view = '&lt;stake_address_here&gt;' 
  AND txi.tx_in_id IS NULL 
  AND block.epoch_no IS NOT NULL 
  AND ma.policy IS NOT NULL 
group by ma.fingerprint, ma.policy 
order by total_quantity desc;
</code></pre>

CC BY-SA 4.0
2024-02-01T11:38:19.660Z
Have you looked at the "interesting queries" document linked from the README in the github repo?
Erik de Castro Lopo
CC BY-SA 4.0
2024-02-01T14:26:48.513Z
Hi Erik, thank you - yes, I have examined those, as well as the schema, and the ERD.

https://github.com/IntersectMBO/cardano-db-sync/blob/master/doc/interesting-queries.md#get-all-assets-with-quantities-and-count-of-mints

There is the "Get all assets with quantities and count of mints" query in the interesting queries doc, but it does not retrieve for a given address / stake address..?
RelevantData
CC BY-SA 4.0
2024-02-01T19:01:20.687Z
I added what I have so far as an edit to my question - see what you think could be better or should be done different @ErikdeCastroLopo ?
RelevantData
CC BY-SA 4.0
2024-02-02T02:13:02.060Z
I have not tried to run that query, but it seems ok,
Erik de Castro Lopo
<cardano-node><cardano-db-sync>
cardano-db-sync sql query for assets of a wallet
# cardano-db-sync sql query for assets of a wallet

(Asked by: username_0 on 2024-02-01)

I am trying to develop the PostgreSQL query that will return all the assets of a given stake address and their respective quantities.
What needs to be joined and queried in order to determine what's in a given wallet?
If I query for the tx_out's of a given stake address:
select * from tx_out where stake_address_id = X order by tx_id desc;
EDIT:
Here is what I have so far, with summed quantities for grouped fingerprints and not yet with sum of ADA added to result, just the assets...
EDIT 2: With policy added, ordered by quantity of asset. I think is what I wanted, just does not have quantity of ADA row in results yet.
SELECT encode(ma.policy::bytea, 'hex') as policy, ma.fingerprint, SUM(matxo.quantity) AS total_quantity
FROM tx_out AS txo
LEFT JOIN tx_in AS txi ON txo.tx_id = txi.tx_out_id AND txo.index::smallint = txi.tx_out_index::smallint 
LEFT JOIN tx ON tx.id = txo.tx_id
LEFT JOIN block ON tx.block_id = block.id 
LEFT JOIN stake_address AS sa ON txo.stake_address_id = sa.id 
LEFT JOIN ma_tx_out AS matxo ON matxo.tx_out_id = txo.id 
LEFT JOIN multi_asset AS ma ON ma.id = matxo.ident 
WHERE sa.view = '' 
  AND txi.tx_in_id IS NULL 
  AND block.epoch_no IS NOT NULL 
  AND ma.policy IS NOT NULL 
group by ma.fingerprint, ma.policy 
order by total_quantity desc;

**Tags:** cardano-node, cardano-db-sync
> **Comments:**
> **username_1**: Have you looked at the "interesting queries" document linked from the README in the github repo?  
> **username_0**: Hi Erik, thank you - yes, I have examined those, as well as the schema, and the ERD.  https://github.com/IntersectMBO/cardano-db-sync/blob/master/doc/interesting-queries.md#get-all-assets-with-quantities-and-count-of-mints  There is the "Get all assets with quantities and count of mints" query in the interesting queries doc, but it does not retrieve for a given address / stake address..?  
> **username_0**: I added what I have so far as an edit to my question - see what you think could be better or should be done different @ErikdeCastroLopo ?  
> **username_1**: I have not tried to run that query, but it seems ok,  

---

## 1 answers

### Answer by username_0 on 2024-02-01. Score: -1

Here is what I have found returns the assets, and their respective quantities, for a given stake address. Enjoy!

`SELECT encode(ma.policy::bytea, 'hex') as policy, ma.fingerprint, SUM(matxo.quantity) AS total_quantity FROM tx_out AS txo LEFT JOIN tx_in AS txi ON txo.tx_id = txi.tx_out_id AND txo.index::smallint = txi.tx_out_index::smallint LEFT JOIN tx ON tx.id = txo.tx_id LEFT JOIN block ON tx.block_id = block.id LEFT JOIN stake_address AS sa ON txo.stake_address_id = sa.id LEFT JOIN ma_tx_out AS matxo ON matxo.tx_out_id = txo.id LEFT JOIN multi_asset AS ma ON ma.id = matxo.ident WHERE sa.view = '' AND txi.tx_in_id IS NULL AND block.epoch_no IS NOT NULL AND ma.policy IS NOT NULL group by ma.fingerprint, ma.policy order by total_quantity desc;`


---

