CODEWITHSUNDEEP
mysql
Danilo
Danilo

Reputation: 382

How to return quantity of repeated values in this case?

In mysql, I have the following table 'Log':

id_user       cod          info
  1            A        random_info1
  1            A        random_info1
  1            A        random_info1
  1            A        random_info2
  1            B        random_info2
  1            B        random_info2
  1            B        random_info3
  1            B        random_info3
  2            A        random_info4
  2            A        random_info4
  2            B        random_info5
  2            B        random_info5
  2            B        random_info5
  2            B        random_info6

With this query:

SELECT
  id_user,
  SUM(cod = 'A') as A,
  SUM(cod = 'B') as B
FROM
  Log
GROUP BY
  id_user

I get the quantity that each 'cod' appears for each 'id_user:

id_user        A       B
  1            4       4
  2            2       4

Now, plus the previous result, I need the number of times a value in 'info' that we've already seen appears again, for each 'cod', like:

id_user        A       B       A_repeated       B_repeated
  1            4       4          2                2
  2            2       4          1                2

A_repeated of id_user = '1' is 2 because random_info1 appears twice after its first appearance.

B_repeated of id_user = '1' is 2 because random_info2 and random_info3 appear once (each) their first appearance.

What changes I need to do in my query to get these results?

Upvotes: 1

Views: 66

Answers (1)

fancyPants
fancyPants

Reputation: 51928

Here you go :)

SELECT
  id_user,
  SUM(cod = 'A') as A,
  SUM(cod = 'B') as B,
  SUM(cod = 'A' && is_repeating) AS A_repeating,
  SUM(cod = 'B' && is_repeating) AS B_repeating
FROM
(
select
l.*
, @is_repeating := if(@prev_id_user = id_user && @prev_cod = cod && @prev_info = info, 1, 0) as is_repeating
, @prev_id_user := id_user
, @prev_cod := cod
, @prev_info := info
from
Log l
, (select @prev_id_user:=null, @prev_cod:=null, @prev_info:=null, @is_repeating:=0) var_init
order by id_user, cod, id
) Log  
GROUP BY
  id_user

I need to add though, that I added an auto_increment column which I used in the order by (which is important!). Like I said already in my comment above there is no order in a database table unless you specify it. You can not rely on the same order from a select without an order by clause, even when it looks like you can do.

UPDATE:

It seems I misunderstood you a little. Here's a corrected version with your desired result of 3 for A_repeating:

SELECT
  id_user,
  SUM(cod = 'A') as A,
  SUM(cod = 'B') as B,
  SUM(cod = 'A' && is_repeating) AS A_repeating,
  SUM(cod = 'B' && is_repeating) AS B_repeating
FROM
(
select
l.*
, @is_repeating := if(@prev_id_user = id_user && @prev_cod = cod && @prev_info = info, 1, 0) as is_repeating
, @prev_id_user := id_user
, @prev_cod := cod
, @prev_info := info
from
Log l
, (select @prev_id_user:=null, @prev_cod:=null, @prev_info:=null, @is_repeating:=0) var_init
order by id_user, cod, info
) Log  
GROUP BY
  id_user

Upvotes: 1

Related Questions