CODEWITHSUNDEEP
mysqlsql
Max
Max

Reputation: 940

Query to calculate the count of numbers that occur in the previous rows

I have a table view like this:

Id, Picks
5, 1
5, 5
5, 10
5, 20

4, 8
4, 10
4, 11
4, 22

3, 1
3, 8
3, 10
3, 25

2, 3
2, 5
2, 23
2, 24

1, 14
1, 17
1, 20
1, 24

with two columns Id, and Picks. The id is repeated four times for each draw which has 4 numbers between 1-25.

I'd like to display the count of each draw numbers that occur in the previous 3 draws. So for the numbers of the draw with id=5, if these numbers occur once in the draws with ids 4,3, and 2, then they are counted.

So for the above example the count would be like this:

Id, Count
5,  3
4,  2
etc.

How could I get this result with a mysql query? the table view doesn't have a unique id.

Upvotes: 0

Views: 35

Answers (2)

The Impaler
The Impaler

Reputation: 48770

I guess you need something like:

select
  a.id, count(distinct b.picks)
from my_table a
join my_table b on b.picks = a.picks
               and b.id between a.id - 3 and a.id - 1
group by a.id

Upvotes: 3

forpas
forpas

Reputation: 164089

With EXISTS:

select 
  t.id,
  sum(
    case when exists (
        select 1 from tablename 
        where (id between t.id - 3 and t.id - 1) and picks = t.picks
      ) then 1
      else 0
    end
  ) counter
from tablename t 
group by t.id
order by t.id desc

See the demo.
Results:

| id  | counter |
| --- | ------- |
| 5   | 3       |
| 4   | 2       |
| 3   | 0       |
| 2   | 1       |
| 1   | 0       |

Upvotes: 2

Related Questions