-4

I am new to Sql and have a question. I have a table as follows.

enter image description here

I want to write a query that will give me for every Month field go back 6 months and count the number of occurrences of that ID. For eg: something like this.

enter image description here

How can we do this in sql? Probably self-join?

Thom A
  • 88,727
  • 11
  • 45
  • 75
user11429634
  • 27
  • 4
  • 4
    SQL Server <> MySQL; what RDBMS are you *really* using? Also [please do not upload images of code/data/errors when asking a question.](//meta.stackoverflow.com/q/285551) – Thom A Jun 28 '22 at 14:47

1 Answers1

0

The key here is to group by, this will give you the distinct rows and then you can do a count on how many times the ID was in the list. The problem is what you want with the month, it seems with your outcome you want to have the latest month but not sure here. The query would look something like this:

Select [Month] = max(month) 
, ID
, [Active_in_6_mnts] =  Count(ID)
from table
group by ID
Tjaym
  • 77
  • 7