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

Asked

Viewed 27 times

0

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");

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 on Sqlfiddle

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

Explanation

inserir a descrição da imagem aqui

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

inserir a descrição da imagem aqui

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

  • From now on, thank you very much! In my case, I would need the records of Uro to count as only 1 for having the same teams and if Uro has a third record with team 2, for example, count 2 records.

  • http://sqlfiddle.com/#! 9/b6baa9/1 In this case I would need to show Gabriel 2, John 1 and Peter 1 in descending order.

  • I updated the link, take a look.

  • Perfect! I didn’t know this method you used. Thank you very much!

  • If I helped you, mark it as the best answer, to help other people with the same question.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.