Group By Function

[itorient]

Hello, I have the following data set that is already grouped by sale_item.

pid sale_item sale_deptno
1 book 101
2 book 101
3 book 102
4 book 103
5 book 101
6 book 101
7 book 102
1 cd 101
2 cd 103
3 cd 102
4 cd 101
1 basket 104
2 basket 100
3 basket 101

Here is what I want to accomplish.

Within the group of sale_item, I would like to count only when there is a change in sale_deptno or count it when there is a break within that group...

And finally sum them up by sale_deptno.

So for this data set:

100 = 1
101 = 5
102 = 3
103 = 2
104 = 1

Any help and expert insight will be appreciated.

 

[donutman]

As long as the PID values are sequentially ordered as shown, then there is an easy solution.
Select * INTO #Temp from YourTable
Delete T1
from (Select * from #Temp) T1 inner join (Select * from #Temp) T2 on T1.PID=T2.PID+1 and T1.Sale_Item=T2.Sale_Item and T1.Sale_DeptNo=T2.Sale_DeptNo
Select Sale_DeptNo, count(*) from #Temp group by Sale_DeptNo
-Karl

 

[nigelrivett]

In the above
(Select * from #Temp)
can be replaced by just #Temp.

You can do it in a single query

select sale_deptno, count(*)
from
(
select *
from tbl t1
where not exists
(select * from tbl t2 where t1.sale_deptno = t2.sale_deptno and t1.sale_item = t2.sale_item and t1.pid+1 = t2.pid)
) a
group by sale_deptno



======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[donutman]

And I thought I was being clever.
-Karl

 

[itorient]

You both are awesome. Thank you very much. This is exactly what I need.