Dears, my data consists of following informations; customer ID, amount of transaction and counterparty region. For each region I would like to count average of volume of transactions. My idea is to use subqueries, but I stucked. This is my current idea, which does not work.
select avg(count_for_DK) from data where
(select count(amount) as count_for_DK, customer_id from data
where country = 'DK'
group by customer_id, country
order by customer_id asc)
Anyone please help me?
Screen with sample of my table: https://ibb.co/BZXDL4Q
As per my understanding you want to get customer wise average amount where country ='DK' If this logic is what you are looking for then below query will work:
select customer_id ,avg(amount) as average_for_DK from data
where country = 'DK'
group by customer_id
order by customer_id
If you want to get the average of number of transactions done by customers of country 'DK'
select avg(count_for_DK) from (
select customer_id ,count(amount) as count_for_DK from data
where country = 'DK'
group by customer_id
order by customer_id) t
If you want to get the average of number of transactions done by customers of each country:
select country,avg(count_for_country) from (
select country,customer_id ,count(amount) as count_for_country from data
group by country,customer_id
order by country,customer_id) t
group by country