MySql search criteria with criteria

Question

I have a table named clients that stores client data

id | fname | lname --- | ------ | ------ 1 | John | Smith 2 | Mike | Bolton 3 | Liz | John 4 | Mark | Jobs 

And I have another table with names that supports every call made to every customer.

 id | timestamp | customer_id | campaign | answered | 1 |2016-09-05 15:24:08| 1 | 2016-09 | 1 | 2 |2016-09-05 15:20:08| 2 | 2016-09 | 1 | 3 |2016-08-05 15:20:08| 2 | 2016-08 | 1 | 4 |2016-08-05 13:20:08| 3 | 2016-08 | 1 | 5 |2016-08-01 15:20:08| 3 | 2016-08 | 0 | 5 |2016-08-01 12:20:08| 4 | General | 1 | 

Campaign General Not taken into account in the calculations.

I need to get a list of clients sorted by call quality ranking based on each client’s history.

This list is used to call clients to:

• Has not been called into a valid calling campaign (e.g. 2016-09 )
• less calls
• Best% answer (total number of calls or total calls)

It should look something like this:

 | id | fname | lname | %ans | called actual campaign | total calls | rank | |----|--------|-------|------|------------------------|-------------|------| | 4 | Mark | Jobs | N/A | no | 0 | 1 | | 3 | Liz | John | 50 | no | 2 | 2 | | 1 | John | Smith | 100 | yes | 1 | 3 | No Show | 2 | Mike | Bolton| 100 | yes | 2 | 4 | No Show 

Please help me!

Answer 1

A request that takes into account for each customer the common calls and callbacks for the specified campaign

 select c.id, count(*) as total_calls, sum(case when answered=1 then 1 else 0 end) as answered_calls from customer c join calls cs on c.id=cs.customer_id where cs.campaign='2016-09' group by c.id 

Then you can use the above query as a subquery for order

 select sub.id, (@rank: =@rank +1) as rank from (the subquery above) sub, (select @rank:=1) order by case when sub.total_calls=0 then 0 else 1, sub.total_calls, sub.answered_calls*100/sub.total_calls 

You can include any required columns in the query result