How do I count the number of records, where equal records are grouped and equals only one, and impose an order on the display

0

I have the relationship table with the columns author and time .

I need to count the number 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. Thus, when displaying in descending order would be:

Gabriel = 2 records

John = 1 record

I can not seem to get past this:

$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");
    
asked by anonymous 28.05.2018 / 18:43

1 answer

0

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 in SQLfiddle

link

Explanation

Inthisfirstexample,thereistheactualdataofthetable,maurohastworecords,felipeonerecordandjesusonerecordtoo.

Using COUNT(DISTINCT time) I get the actual query values

    
28.05.2018 / 18:59