Select with group by double

Question

Select with group by double

Navigation

#1 by (2 votes)

0

In MySQL I have a table named contact_status :

id | id_contato | detalhamento_id
1  |     1      |       2
2  |     1      |       3
3  |     2      |       4
4  |     2      |       2
5  |     2      |       1
6  |     3      |       2 
7  |     4      |       4
8  |     5      |       4

In it all the contact contacts of the site are recorded, and also whenever a status is modified (for history). What I need now is to list the total of the detail_id of each type. I got it with GROUP BY without problems. But it's not the right one. Because it also takes the duplicate entries (if a user has the modified status). So you would need to get only the detail_id of each user, but the last, with the largest ID. What I need to be shown is this:

detalhamento 1 - 1 ocorrência
detalhamento 2 - 1 ocorrência
detalhamento 3 - 1 ocorrência
detalhamento 4 - 2 ocorrência

How to do it?

php mysql group-by select count

asked by anonymous 10.07.2018 / 14:58

1 answer

C ++ program stops responding when calling a function for the second time How to transform a list into set in Python?

score 2 · Accepted Answer

The simplest way (perhaps not the most elegant) is to do this with a subquery, like this:

Select contato_status.detalhamento_id, count(*) ocorrencias 
From contato_status 
Inner Join (Select id_contato ,MAX(id) id 
            From contato_status 
            Group By id_contato) NovaTabela
    on contato_status.id = NovaTabela.id 
Group By contato_status.detalhamento_id;

I've added a SQLFiddle to see how the query works.

How it works:

The subquery creates creates a "virtual table" (I prefer to use the set term, "set" in English), called NewTable, with the id_contact and the maximum id. You can see the result here: Isolated SubQuery

Now it is to join the set with the main table used the ID as its criterion, grouping by detail_id and counting.

As you can see, the result is:

detalhamento 1 - 1 ocorrência (para o contato_id 2)
detalhamento 2 - 1 ocorrência (para o contato_id 3)
detalhamento 3 - 1 ocorrência (para o contato_id 1)
detalhamento 4 - 2 ocorrências (para os contato_id 4 e 5)