su919 February 2016

how to find repeated value and count in mysql

I have table with 3 columns, now how find value if it appears next 3 times immediately i.e 1st trnas_value appears in next 3 consecutive times (repeaded 4 times) and 2nd and 6th also rows also repeated the same.date column is sorted from A_Z

date               tran_val                name
23mar                22                    mark
24mar                22                    mark
25mar                22                    mark
26mar                22                    mark
27mar                22                    mark
28jan                99                    john
29jan                99                    john
30jan                99                    john
31jan                99                    john

output

name     trans_value        consecutive_count
mark       22                   2
john       99                   1

we have a code which is not giving the above output..

SELECT name, 
        tran_val, 
        MAX(cnt - 3) AS consecutive_count
FROM
(
    SELECT date, 
            tran_val, 
            name, 
            @cnt:=IF(@tran_val=tran_val AND @name=name, @cnt + 1, 1) AS cnt,
            @tran_val:=tran_val,
            @name:=name
    FROM some_table
    CROSS JOIN (SELECT @cnt:=0, @tran_val:=0, @name:='') sub0
    ORDER BY `date`
) sub1
GROUP BY name, 
        tran_val

any modification in the above code which will get desired output.thanks

Answers


Giorgos Betsos February 2016

Try this:

SELECT `tran_val`, `name`, COUNT(*) - 3
FROM (
  SELECT `date`, `tran_val`, `name`, rn - seq AS grp
  FROM (
    SELECT `date`, `tran_val`, `name`,
           @rn := @rn + 1 AS rn,
           @seq := IF(@name = `name` And @val = `tran_val`, @seq+1, 1) AS seq,
           @name := name,
           @val := tran_val
    FROM mytable
    CROSS JOIN (SELECT @rn := 0, @seq := 0, @name = '', @val = 0) AS vars
    ORDER BY `date`) AS t ) AS s
GROUP BY `tran_val`, `name`, grp
HAVING COUNT(*) > 3

You need two separate variable to enumerate sequences:

  • @rn just enumerates consecutive table rows
  • @seq enumerates consecutive table rows having the same name, tran_val values.

The difference between these two variables, i.e. @rn - @seq, identifies islands of consecutive table rows having the same name, tran_val values.

Edit: I added a HAVING clause to the query so as to filter out islands having a population of 3 or less consecutive rows.

Demo here

Post Status

Asked in February 2016
Viewed 3,632 times
Voted 14
Answered 1 times

Search




Leave an answer