I have this data:
ARRIVAL,ITEM_TYPE,ITEM
1,0,Cat
2,0,Dog
3,1,Horse
4,1,Cow
5,0,Fish
6,0,Barn
7,0,Potato
I would like to query for this result:
0,2
1,2
0,3
... showing that in order by ARRIVAL there was a group of 2 items with ITEM_TYPE = 0, then 2 with ITEM_TYPE = 1, then another group with ITEM_TYPE = 0. The goal is to provide information about the arrival in a system of similar groups of records; Cat and Dog are in the same ITEM_TYPE as Fish, Barn, and Potato, but they didn't arrive in the same time-series group. I can't figure out how to use COUNT,DISTINCT,and GROUP BY in a way that doesn't collapse the two groups of zeroes into a single line like this:
0,5
I will appreciate any help.
-- Bob
You have a gaps and islands problem here, you could use the difference between two row_numbers approach to solving it :
SELECT ITEM_TYPE, COUNT(*) AS CNT
FROM (
SELECT *,
ROW_NUMBER() OVER (ORDER BY ARRIVAL)
- ROW_NUMBER() OVER (PARTITION BY ITEM_TYPE ORDER BY ARRIVAL) AS grp
FROM mytable
) t
GROUP BY grp, ITEM_TYPE
ORDER BY ARRIVAL;
Results :
ITEM_TYPE CNT
0 2
1 2
0 3