I require the following data: e.g. sweden male brandA EUR sweden female brandA EUR sweden male brandB EUR england male brandA EUR england female brandA EUR england male brandB EUR england female brandC EUR
EUR came from the fact that of 8000 people, 5000 use euro, 2000 use gbp and 1000 use SEk . SO the most popular currency is EURO. I would like it displayed for every line
to get the most popular currency, max_curr
and for every row returned to have a column called max_curr with this value displayed
select
cdl.country
,cd.gender
,cd.brand
,t.max_curr
from customer_data_list cdl
left join customer_data cd on cd.customerid = cdr.customerid
left join
(
select
trans.customerid, a.*
from
(
select
a.*
,max(count_currency) over() as max_curr
FROM
(
select
t.currency
,count(t.currency) as count_currency
from transactions t
where t.function = 'DEPOSIT' and t.status = 'ACCEPTED'
group by t.currency
order by currency
) a
) a
left join transactions trans on trans.currency = a.currency
where count_currency = max_curr) t on t.customerid = cdl.customerid
This is not returning what I require because most max_curr column entries are empty. Can someone help please?
If, for each customer, you want the currency that is used most often for ACCEPTED, DEPOSIT transactions, then you can use aggregation and window functions:
select cc.*
from (select t.customerid, t.currency, count(*) as cnt,
row_number() over (partition by t.customerid order by count(*) desc) as seqnum
from transactions t
where t.function = 'DEPOSIT' and
t.status = 'ACCEPTED'
group by t.customerid, t.currency
) cc
where seqnum = 1;
EDIT:
For the revised interpretation of the question, just use a cross join:
select t.customerid, t.currency, count(*) as cnt,
tt.currency as most_popular_currency
from transactions t cross join
(select t.currency
from transactions t
where t.function = 'DEPOSIT' and
t.status = 'ACCEPTED'
group by t.currency
order by count(*) desc
limit 1
) tt
where t.function = 'DEPOSIT' and
t.status = 'ACCEPTED'
group by t.customerid, t.currency
Thank you for replying. I would like the overall most popular currency to be displayed for all customers . So in other words, the column would be the same for all. If EUR is the most popular overall, then the column max_curr would be EUR for all customers