Having and Count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Having and Count

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-06-22 : 21:24:37

Please see this sample data.

SELECT *
INTO #TEMP
FROM (
select '031610-00612' as ID, '09/08/2004' as Tdate, 'Money(4)' as Category, '2000 pesos(2 mos)' as Item, '4' as Q
union all
select '031610-00612', '09/08/2004', 'Money(4)', '2000 pesos(2 mos)', '4'
union all
select '031610-00612', '09/08/2004', 'Money(4)', '2000 pesos(2 mos)', '4'
union all
select '031610-00612', '09/08/2004', 'Money(4)', '2000 pesos(2 mos)', '4'
union all

select '031610-11111', '09/08/2004', 'Money(4)', '3000 pesos(2 mos)', '4'
union all
select '031610-11111', '09/08/2004', 'Money(4)', '3000 pesos(2 mos)', '4'
union all
select '031610-11111', '09/08/2004', 'Money(4)', '3000 pesos(2 mos)', '4'
union all

select '031610-11111', '09/08/2004', 'Money(4)', '2000 pesos(2 mos)', '4'
union all
select '031610-11111', '09/08/2004', 'Money(4)', '2000 pesos(2 mos)', '4'
union all
select '031610-11111', '09/08/2004', 'Money(4)', '2000 pesos(2 mos)', '4'
union all
select '031610-11111', '09/08/2004', 'Money(4)', '2000 pesos(2 mos)', '4'
) as tblTemp

select *
from #TEMP

select ID, Tdate, Category, ITem, q
from #TEMP
group by ID, Tdate, Category, ITem, q
having count(ID) <> q

drop table #TEMP

RESULT IS THIS...


ID----------	Tdate-----	Category-	Item--------------Q
-------------------------------------------------------------
031610-00612	09/08/2004	Money(4)	2000 pesos(2 mos)	4
031610-00612	09/08/2004	Money(4)	2000 pesos(2 mos)	4
031610-00612	09/08/2004	Money(4)	2000 pesos(2 mos)	4
031610-00612	09/08/2004	Money(4)	2000 pesos(2 mos)	4

031610-11111	09/08/2004	Money(4)	3000 pesos(2 mos)	4
031610-11111	09/08/2004	Money(4)	3000 pesos(2 mos)	4
031610-11111	09/08/2004	Money(4)	3000 pesos(2 mos)	4

031610-11111	09/08/2004	Money(4)	2000 pesos(2 mos)	4
031610-11111	09/08/2004	Money(4)	2000 pesos(2 mos)	4
031610-11111	09/08/2004	Money(4)	2000 pesos(2 mos)	4
031610-11111	09/08/2004	Money(4)	2000 pesos(2 mos)	4

My Select count having statement...
ID----------	Tdate-----	Category-	Item--------------Q
-------------------------------------------------------------
031610-11111	09/08/2004	Money(4)	3000 pesos(2 mos)	4

The datas that i want to return are those in red.
Please show me how?

What i want is to retrieve those datas whose rows is not equal to Q.

TNX...

Want Philippines to become 1st World COuntry? Go for World War 3...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-23 : 01:26:46

[code]
Select T1.* from #temp T1 inner join (
Select Id,Tdate,item,category,q from #temp group by ID, Tdate, Category, ITem,q
having count(*)<>q) T2 on T1.id=T2.Id where T1.Tdate=T2.Tdate and T1.Item=T2.Item and T1.category=T2.Category
and T1.q=T2.q
[/code]

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources