Getting count hour wise - 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 2005 Forums

Transact-SQL (2005)

Getting count hour wise

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-10-13 : 14:18:59

Hi guys,
I have a table like this
Declare @tbl as table(Hrs int,Reading int,Type int)
Insert into @tbl
Select 10,25,1 union all
Select 10,10,1 union all
Select 10, 0,1 union all
Select 10,15,1 union all
Select 10,10,2 union all
Select 10,10,2 union all
Select 11,10,1 union all
Select 11,35,1 union all
Select 11,0,1 union all
Select 11,0,1 union all
Select 11,10,2 union all
Select 11,10,2

I want to get the count of readings hour wise where the type is 1 and where ever the type is 1 and if the reading 0 that record should not be considered in count.

So the output will be

Hrs Reading Type Count
10 ---- 25 ---- 1 ---- 3 (Since the 3rd record reading is 0 hence the value of the count is 3 else wd had been 4)
10 ---- 10 ---- 1 ---- 3
10 ---- 0 ---- 1 ---- 3
10 ---- 15 ---- 1 ---- 3
10 ---- 10 ---- 2 ---- 3
10 ---- 10 ---- 2 ---- 3
11 ---- 10 ---- 1 ---- 2(9th & 10th record readings are 0 hence the value of the count is 2 else wd had been 4)
11 ---- 35 ---- 1 ---- 2
11 ---- 0 ---- 1 ---- 2
11 ---- 0 ---- 1 ---- 2
11 ---- 10 ---- 2 ---- 2
11 ---- 10 ---- 2 ---- 2

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 14:26:59

SELECT Hrs, Reading, Type, SUM(CASE WHEN Type = 1 AND Reading > 0 THEN 1 ELSE 0 END) OVER (PARTITION BY Hrs) AS [Count]
FROM @tbl

E 12°55'05.63"
N 56°04'39.26"

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-10-13 : 14:39:52

Thanks Peso.
You did it again.I wanted to know one more thing.Is it possible to put some conditional clause in the partition function.
Say for example some thing like (PARTITION BY Hrs where....)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 14:51:53

PARTITION BY CASE ... WHEN ... ELSE .. END

will work ok.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources