I have a transaction table like this:

tr_id    merchant customer  amount
-------------------------------------
00001    2005     3002      20
00002    2006     3002      11.16
00003    2001     3007      17.91
00004    2005     3002      20
00005    2003     3003      13.23
00005    2006     3007      14.61
00006    2005     3002      20 
Etc.

I want to know the query to find which merchant that have >80% of the transaction coming from the same customer.

The result should contain list of merchant, customer in question, and each with sum of amount.

CodePudding user response：

1. Group the table once by merchant to calculate the total records of each merchant
2. Once again, group the table by merchant and customer to calculate the total records of each merchant and customer
3. Join the result and calculate the percentage

query :

SELECT
       t2.merchant,
       t2.customer,
       CONCAT((CAST(t2.merchant_Customer_CuontItem AS FLOAT) / CAST(t1.merchant_CuontItem AS FLOAT)) * 100, '%') AS newCol
FROM    
  (SELECT merchant,COUNT(*) AS merchant_CuontItem FROM tb GROUP BY merchant) t1    
JOIN     
  (SELECT merchant,customer ,COUNT(*) AS merchant_Customer_CuontItem FROM tb GROUP BY merchant,customer) t2     
ON t1.merchant = t2.merchant

Demo in db<>fiddle

CodePudding user response：

Try this:

select c.merchant, customer, 100 * customer_total / merchant_total as percentage
from (select merchant, customer, sum(amount) as customer_total
    from mytable
    group by merchant, customer) c
join (select merchant, sum(amount) as merchant_total
    from mytable
    group by merchant) m 
    on m.merchant = c.merchant
      and customer_total > .8 * merchant_total

See live demo.

You could change and to where if you feel it improves readability, at a possible small cost to performance.