Count changes of a value over time?

Question

Hi all,

again I am stuck with counting something in MySQL. The database structure is far from SOers´d call optimal, but nevertheless I do not have an influence here and have to live with it. Probably that´s one of the reasons why I need help again to get some information out of it :)

Assume I have: some_id (not the PK of the table, not unique), year, month (no date fields just two integer fields), some_flag (character that is either A or B) .

Now I´d like to know how often some_flag has changed (in a given time span). The time span is not utterly important in the first approach, I just need to know how many changes happened. Note that changes can only happen monthly. My query:

 SELECT  some_id,year,some_flag FROM mytable
 WHERE some_flag = "A" OR someflag = "B"
 AND year > 2005
 GROUP BY some_id,some_flag
 HAVING COUNT(DISTINCT some_flag) > 1

returns an empty result set. What´s wrong with it? I am sure there are years in which the flag changes over months...

Isn't something like

 select .... , sum(case when month=month-1 and some_flag != some_flag then 1 else 0 end)     as changecount

possible ?

Answer 1

When grouping by some_flag you're making COUNT(DISTINCT some_flag) to be always 1.

Try gouping only by some_id. I hope this helps.

Answer 2

Try this:

SELECT some_flag, COUNT(some_id) FROM mytable
WHERE some_flag = "A" OR someflag = "B"
AND year > 2005
GROUP BY some_flag
HAVING COUNT(some_id) > 1

-Edit-
If you want to see a month over month count, try this:
(Note: it will only show months where it has changed)

SELECT some_flag, year, month, COUNT(some_id) FROM mytable
WHERE some_flag = "A" OR someflag = "B"
AND year > 2005
GROUP BY some_flag, month, year
HAVING COUNT(some_id) > 1

Answer 3

It looks to me like you need to do this in two parts.

First, execute this SQL query to get all of the values for some_id, some_flag:

SELECT some_id, some_flag, year, month
FROM ...
WHERE year > 2005
ORDER BY some_id, some_flag, year, month

Then, run the output through a match / merge process to detect when some_flag changes for a given some_id. Save the year and month that some_flag changes for reporting in the match / merge process.