How do I count the amount of records, where equal records are grouped and equal to only one, and impose an order when showing

Question

I have the table of relation with the columns author and team.

I need to count the amount of records the author has, and the records grouped by author and time count as only 1, and then display.

Example:

Author -------- Time

Gabriel ------- X

Gabriel ------- X

Gabriel ------- Y

John ---------- Z

John ---------- Z

John ---------- Z

In the example, where the author is Gabriel and the team is X would be counted only as 1 record, the same for John. Therefore, to display in descending order would be:

Gabriel = 2 records

John = 1 entry

---

I can’t get past it:

$sql = $pdo->query("SELECT *, COUNT(autor) AS total FROM aa_usuarios_treinamentos_rel WHERE time>'$timeRanking' AND status='true' GROUP BY autor, time ORDER BY total DESC");

Answer 1

According to the scenario you presented, the basic query would look like this

SELECT autor, count(DISTINCT time) FROM tbl GROUP BY autor

See working on Sqlfiddle

http://sqlfiddle.com/#! 9/7c5220/1

Explanation

In this first example is the actual data, from the table, mauro has two records, felipe a record and jesus a record also.

Using the COUNT(DISTINCT time) I receive the real values of the query