How to make MySQL query to calculate percentage?

Please help me to correctly create a query to Display all sellers who have 5 pairs of customers and count 10% of the total purchases of those customers (for a test can pairs cut). Here is what the code now is:
SELECT umr.af_id, 
 (FLOOR(COUNT(*) / 5) * (SELECT SUM(amount_value * 0.1) 
 FROM orders io WHERE io.status='Completed')) AS amount
FROM orders io
LEFT JOIN u_af ua on io.uid = ua.uid 
LEFT JOIN arur umr on umr.af_id = ua.id
GROUP BY af_id
HAVING COUNT(*) MOD 5 = 0;

But he brings something inappropriate.
Tables and code can be viewed at the link: link.
Please help to write this query.
UPD1:
I see the solution:
Af_id to take from the table the arur
Calculate all r_w_uid relevant to this af_id table arur
Count the number of them.
As soon as the total number r_w_uid reached, for example, 6, then sum the values amount_value from the orders table are related to the uid from the orders table, subject to status = Completed.
But I could easily be wrong.
Multiplying the previous value by 0.1 and output the value.
UPD2:
Table relationships:
orders.uid = u_af.uid
u_af.id = arur.af_id

UPD3:
Description of the tables:
1)
orders is a table with information about the payments.
orders.the uid - column with the internal id of the user.
orders.amount_value - column payment amount.
orders.status column with the status of the payment.
2)
u_af - table is needed to link the orders table with the table arur.
3)
arur - table of relations between sellers and customers.
arur.af_id the column with the id of the seller.
arur.r_w_uid - a column with the customer id.

Pairs are formed as follows:
If you look at the spreadsheet arur, we can see that, for example, af_id = 52 there are several entries in the column r_w_uid. If you take all recording related r_w_uid af_id = 52 and divide them by 2, we get the number of pairs. The rest is not included in the pair.
April 9th 20 at 09:52
2 answers
April 9th 20 at 09:54
Solution
You will excuse me, but what You wrote in the query ... it's not nearly what You wanted to in words.
And the words all swam "Display all the customers that have 5 pairs of clients," who on whom stood?

If the 2 words in the query, You have 2 LEFT JOIN, both are not needed.
You have no link between the two instances of orders, and there is no need to 2 times.
Well, in General .... works for a long time and it is not clear that more data will be to work days ...

P. S. after a long clarification any answer is for MySQL 8+
select af_id, sum(coalesce(amount_value,0))*0.1 sum_pair_10
from (
 select 
arur.af_id,
arur.r_w_uid,
 lag(arur.r_w_uid,1,-1) over (partition by arur.af_id order by arur.r_w_uid) r_w_uid_1
 ,row_number() OVER (partition by arur.af_id order by arur.r_w_uid ) mod 2 pair_flag

 from arur, u_af 
 where u_af.id = arur.af_id ) t1 left join orders o on (o.uid in (t1.r_w_uid,t1.r_w_uid_1) and o.status = 'Completed')
where pair_flag = 0
group by af_id
having count(distinct r_w_uid) > 5

For the younger versions
select af_id, sum(coalesce(amount_value,0))*0.1 sum_pair_10
from (SELECT arur.af_id,
 arur.r_w_uid, 
 @row_num := CASE WHEN @row_num_val = af_id THEN @row_num+1
 WHEN (@row_num_val := af_id) IS NOT NULL THEN 1
 END pair_flag,
 @lag_r_w_uid := CASE WHEN (@row_num_val = af_id) and @row_num mod 2 = 1 THEN r_w_uid
 else @lag_r_w_uid
 END r_w_uid_1 

FROM arur, u_af, (SELECT @row_num := null, @row_num_val := null, @lag_r_w_uid := null) AS x
where u_af.id = arur.af_id 
ORDER BY af_id, r_w_uid ) t1 left join orders o on (o.uid in (t1.r_w_uid,t1.r_w_uid_1) and o.status = 'Completed')
where pair_flag mod 2 = 0
group by af_id
having count(distinct r_w_uid) > 5
I added a bit to the description. Maybe this will help? - Titus.Rosenbaum commented on April 9th 20 at 09:57
select
arur.af_id,
sum(o.amount_value) * sum_ 0.1
from arur, u_af left join orders o on (o.uid = u_af.uid and o.status = 'Completed')
where u_af.id = arur.af_id
group by arur.af_id
having count(arur.r_w_uid) > 5

I don't quite understand about the status = Completed, just entered what is considered their only - dahlia.Reilly commented on April 9th 20 at 10:00
Pairs are formed as follows:
If you look at the spreadsheet arur, we can see that, for example, af_id = 52 there are several entries in the column r_w_uid. If you take all recording related r_w_uid af_id = 52 and divide them by 2, we get the number of pairs. The rest is not included in the pair.


You, my dear, say strange things. since You are looking for a number of efimernoe pairs (you just need to divide by 2, there is no dependence in the data is responsible for the pairing) that not morochte head neither himself nor the people. Multiply by 2 your constant with which compare (5 pairs = 10) and describe this condition in the having clause putting ">" or ">=" your sacred values "we get the number of pairs. The rest is not included in the pair."

select
arur.af_id,
sum(o.amount_value) * 0.1 sum_10p
from arur, u_af left join orders o on (o.uid = u_af.uid and o.status = 'Completed')
where u_af.id = arur.af_id
group by arur.af_id
having count(arur.r_w_uid) > 5*2 - dahlia.Reilly commented on April 9th 20 at 10:03
April 9th 20 at 09:56
@Jamaal_Ebert, status = Completed need to filter out unpaid orders. Thank you for your help.
Unfortunately, the query something gives. Do not consider pairs and some odd amount of prints.
For example (all actions and figures from the fiddle for reference): for af_id = 52 sum_ = 900 000 ie its customers must buy 9 000 000 to provided 10% of their amount, it turned out 900 000. Such amount in the test tables no.
Manually considering all relevant af_id = 52 (o.uid = 60), I got 9 clients and a total amount of 1 550 000, i.e. 10% of this amount should be 155 000. But when you consider that it is necessary to get the interest for 4 pairs, it should have 145 000, as the last 9-th customer in a few misses.
Thanks again for the help.
Why do I get 9 000 000?
UPD2:
Table relationships:
orders.uid = u_af.uid
u_af.id = arur.af_id

as You wrote o.uid = 60, which is 9*1 000 000 = 9 000 000

Deal with relationships in tables, selects the first I wrote without downloading data.

Because You need to work with pairs, and that the comments became clear, I will write the below select, but he's still on the old terms. As soon as the conditions adjust, everything should settle quickly.

select af_id, sum(Sum_pair)*0.1 sum_pair_10
from (
 select 
arur.af_id,
 o.amount_value + lag(o.amount_value,1,0) over (partition by arur.af_id order by o.id) Sum_pair,
 row_number() OVER (partition by arur.af_id order by o.id) mod 2 pair_flag

 from arur, u_af left join orders o on (o.uid = u_af.uid and o.status = 'Completed')
 where u_af.id = arur.af_id) t1
where pair_flag = 0
group by af_id
- dahlia.Reilly commented on April 9th 20 at 09:59
so I'm a little confused with dates, I wrote at the top of the twin operation, and You need a pair of customers ...

Write a select that outputs pairs for af_id = 52 and the sort term (who the loser will be the 9th)

And write a bunch of customers with orders, but I do not understand how 1 550 000 for af_id = 52 - dahlia.Reilly commented on April 9th 20 at 10:02
@Jamaal_Ebert, Request relationships with vendors and customers.
-- Request relationships with clients + how much each customer has paid
WITH 
cte1 AS (
SELECT t3.amount_value,
 t2.id dmitriytel, 
 t1.r_w_uid klient
FROM arur t1
U_af JOIN t2 ON t1.af_id = t2.id
JOIN orders t3 ON t1.r_w_uid = t3.uid
)
SELECT dmitriytel, klient, amount_value
FROM cte1
WHERE dmitriytel = 52
ORDER BY dmitriytel;


-- The query for the sum and interest to u_af.id = 52
WITH 
cte1 AS (
SELECT t3.amount_value,
 t2.id dmitriytel, 
 t1.r_w_uid klient
FROM arur t1
U_af JOIN t2 ON t1.af_id = t2.id
JOIN orders t3 ON t1.r_w_uid = t3.uid
)
Dmitriytel SELECT SUM(amount_value) AS summa, SUM(amount_value * 0.1) AS 10_procentov
FROM cte1
WHERE dmitriytel = 52
ORDER BY dmitriytel;

The loser will be the one with the higher arur.r_w_uid. This request arur.r_w_uid = 91 - Titus.Rosenbaum commented on April 9th 20 at 10:05
Because there normal when I have thrown selekt

select af_id, sum(amount_value)*0.1 sum_pair_10
from (
 select 
arur.af_id,
arur.r_w_uid,
 lag(arur.r_w_uid,1,-1) over (partition by arur.af_id order by arur.r_w_uid) r_w_uid_1
 ,row_number() OVER (partition by arur.af_id order by arur.r_w_uid ) mod 2 pair_flag

 from arur, u_af 
 where u_af.id = arur.af_id ) t1 left join orders o on (o.uid in (t1.r_w_uid,t1.r_w_uid_1) and o.status = 'Completed')
where pair_flag = 0
group by af_id
having count(pair_flag) > 5


Is left but it is not clear why we need the table u_af .
left left join ... ie. if the seller has 5 pairs and more customers but no operations, the row appears as it should - I don't know

P. S. can and prettier to write, but I'm afraid you will get confused in the logic of the - dahlia.Reilly commented on April 9th 20 at 10:08
@Jamaal_Ebert, thank you very much for your help. Tell me, please, and what is lag(arur.r_w_uid,1,-1)?
Swears by -1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '-1) - Titus.Rosenbaum commented on April 9th 20 at 10:11
can write lag(arur.r_w_uid), in Your case it does not play a role. lag - indicates the column value with an offset ... read the best in the Internet, they are examples will show. - dahlia.Reilly commented on April 9th 20 at 10:14
@Jamaal_Ebert, Thank You very much. Left, I hope, last question: Inquiry now displays the values for 4 pairs and 5 pairs 5 pairs yet. Why it outputs value? - Titus.Rosenbaum commented on April 9th 20 at 10:17
Task terribly strange, but here goes:

select af_id, sum(coalesce(amount_value,0))*0.1 sum_pair_10
from (
 select 
arur.af_id,
arur.r_w_uid,
 lag(arur.r_w_uid,1,-1) over (partition by arur.af_id order by arur.r_w_uid) r_w_uid_1
 ,row_number() OVER (partition by arur.af_id order by arur.r_w_uid ) mod 2 pair_flag

 from arur, u_af 
 where u_af.id = arur.af_id ) t1 left join orders o on (o.uid in (t1.r_w_uid,t1.r_w_uid_1) and o.status = 'Completed')
where pair_flag = 0
group by af_id
having count(distinct r_w_uid) > 5
- dahlia.Reilly commented on April 9th 20 at 10:20
@Jamaal_Ebert, Thank You. So everything is working. Yeah, the problem really non-standard. Write this code in a separate answer, so I said to him. - Titus.Rosenbaum commented on April 9th 20 at 10:23
@Jamaal_EbertWeird in the fiddle it works, but in the real MySQL database, no. Swears: #1064 - you Have an error in the query. Refer to the documentation for the version of MySQL on the subject of correct syntax near '(partition by arur.af_id order by arur.r_w_uid) r_w_uid_1,
'on line 6 - Titus.Rosenbaum commented on April 9th 20 at 10:26
what version of MySQL? - dahlia.Reilly commented on April 9th 20 at 10:29
@Jamaal_Ebert, 5.5.67-MariaDB - Titus.Rosenbaum commented on April 9th 20 at 10:32
I don't really like the syntax to MySQL 8, here's something in the old style:
select af_id, sum(coalesce(amount_value,0))*0.1 sum_pair_10
from (SELECT arur.af_id,
 arur.r_w_uid, 
 @row_num := CASE WHEN @row_num_val = af_id THEN @row_num+1
 WHEN (@row_num_val := af_id) IS NOT NULL THEN 1
 END pair_flag,
 @lag_r_w_uid := CASE WHEN (@row_num_val = af_id) and @row_num mod 2 = 1 THEN r_w_uid
 else @lag_r_w_uid
 END r_w_uid_1 

FROM arur, u_af, (SELECT @row_num := null, @row_num_val := null, @lag_r_w_uid := null) AS x
where u_af.id = arur.af_id 
ORDER BY af_id, r_w_uid ) t1 left join orders o on (o.uid in (t1.r_w_uid,t1.r_w_uid_1) and o.status = 'Completed')
where pair_flag mod 2 = 0
group by af_id
having count(distinct r_w_uid) > 5
- dahlia.Reilly commented on April 9th 20 at 10:35
@Jamaal_Ebert, Excuse me for being so puzzled and You have wasted Your time. Thank You very much. Everything works. - Titus.Rosenbaum commented on April 9th 20 at 10:38
@Jamaal_Ebert, Excuse me, please. Found one problem: the result should only get those sellers the required number of pairs of customers who have paid for the order (orders.status = 'Completed') and are, apparently, all who have just the required number of pairs (arur.r_w_uid). For example: if you change the number of pairs is 3, then we get two entries.
1) af_id = 12, although he only has 4 customers with payments with status = 'Completed' - a problem with him
2) af_id = 52 it 9 customer payment with status = 'Completed' - no problem.
Is there any way to fix it?
fiddle - Titus.Rosenbaum commented on April 9th 20 at 10:41
I'm talking about the situation mentioned above, if you use a left join will output them, if not just join, I don't know how You had.

select af_id, sum(coalesce(amount_value,0))*0.1 sum_pair_10
from (SELECT arur.af_id,
 arur.r_w_uid, 
 @row_num := CASE WHEN @row_num_val = af_id THEN @row_num+1
 WHEN (@row_num_val := af_id) IS NOT NULL THEN 1
 END pair_flag,
 @lag_r_w_uid := CASE WHEN (@row_num_val = af_id) and @row_num mod 2 = 1 THEN r_w_uid
 else @lag_r_w_uid
 END r_w_uid_1 

FROM arur, u_af, (SELECT @row_num := null, @row_num_val := null, @lag_r_w_uid := null) AS x
where u_af.id = arur.af_id 
ORDER BY af_id, r_w_uid ) t1 join orders o on (o.uid in (t1.r_w_uid,t1.r_w_uid_1) and o.status = 'Completed')
where pair_flag mod 2 = 0
group by af_id
having count(distinct r_w_uid) > 5
- dahlia.Reilly commented on April 9th 20 at 10:44
@Jamaal_EbertAlready figured out. It's my fault, sorry. - Titus.Rosenbaum commented on April 9th 20 at 10:47

Find more questions by tags MySQL