Getting sum 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 sum hour wise

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-10-16 : 05:14:06

Hi guys,

I want to group the records according to the hour.I have a table like this

--------------- Time ----------Primary Instrument -----Second Instrument
Reading 1 5/29/2008 14:30 --------304 -------------------------4.29
Reading 2 5/29/2008 14:40 --------435 -------------------------3.52
Reading 3 5/29/2008 14:50 --------512 -------------------------0

When grouping the record the output should be like this
Time Primary Instrument Second Instrument
2 PM---------- 1251---------- 94.62

While displaying hour wise reading, the second instruments reading will be sum of all the records of second inst divided by the sum of readings of primary inst in that hour.I have achieved this.But my problem is if any of the second inst reading is 0 then the corresponding primary inst reading shud not be considered when calculating the second instrument reading(i.e sum of primary inst reading).But while displaying primary inst reading all the readings of primary inst needs to be summed up irrespective whether the corresponding second inst reading is 0.
I hope I have explained the problem to best of my ability.
Thanks for any help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 06:51:53

[code]SELECT DATEPART(hh,DATEADD(hh,DATEDIFF(hh,0,Time),0)),SUM([Primary Instrument ]) AS PrimaryInstrument,
SUM([Secondary Instrument ]) AS SecondaryInstrument
FROM Table
GROUP BY DATEPART(hh,DATEADD(hh,DATEDIFF(hh,0,Time),0))[/code]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-10-16 : 07:06:13

Visakh you query just gives sum hour wise.I think you did not read my further requirements below.

While displaying hour wise reading, the second instruments reading will be sum of all the records of second inst divided by the sum of readings of primary inst in that hour.I have achieved this.But my problem is if any of the second inst reading is 0 then the corresponding primary inst reading shud not be considered when calculating the second instrument reading(i.e sum of primary inst reading).But while displaying primary inst reading all the readings of primary inst needs to be summed up irrespective whether the corresponding second inst reading is 0.
I hope I have explained the problem to best of my ability.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-10-16 : 10:06:53

Isnt there anyone who can solve it?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 12:40:04

Sorry i was in a hurry and missed the explanation first time. seems like what you want is this

SELECT DATEPART(hh,DATEADD(hh,DATEDIFF(hh,0,Time),0)),SUM([Primary Instrument ]) AS PrimaryInstrument,
CASE WHEN SUM(CASE WHEN [Secondary Instrument ]=0 THEN 1 ELSE 0 END)>0 THEN SUM([Secondary Instrument ]) ELSE SUM([Secondary Instrument ])*1.0/SUM([Primary Instrument ]) END AS SecondaryInstrument
FROM Table
GROUP BY DATEPART(hh,DATEADD(hh,DATEDIFF(hh,0,Time),0))

i didnt understand

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-10-17 : 03:19:22

Visakh thanks for the reply.
But the output for Secondary Instrument should be 94.62.The sum of Primary Instrument should be divided by sum of Secondary Instrument.
But when dividing if the Secondary Instrument reading is 0 then the corresponding Primary Instrument reading should not be considered.
So the division should be with 304 + 435 only.But while displaying the Primary Instrument readings it should show 304 + 435 + 512 which is 1251.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 04:58:23

[code]SELECT DATEPART(hh,DATEADD(hh,DATEDIFF(hh,0,Time),0)),SUM([Primary Instrument ]) AS PrimaryInstrument,
SUM(CASE WHEN [Secondary Instrument ]> 0 THEN ([Primary Instrument ] ELSE 0 END)*1.0/SUM([Secondary Instrument ]) AS SecondaryInstrument
FROM Table
GROUP BY DATEPART(hh,DATEADD(hh,DATEDIFF(hh,0,Time),0))[/code]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-10-17 : 05:20:20

Visakh thank you very much.It worked.

But I have another problem.I hope you dont mind in helping in that.

declare @tbl as table(id int identity (1,1),dayno int,type int,reading float)
insert into @tbl
select 1,1,28125.9800 union all
select 1,2,1.2297 union all
select 1,3,0.0000 union all
select 2,1,32911.5600 union all
select 2,2,1.3794 union all
select 2,3,0.0000 union all
select 14,1,923891.4200 union all
select 14,2,0.0000 union all
select 14,3,0.0000

Here I have to check type.If type is 2 & the reading is 0 then the corresponding reading of type of that day where it is
1 should not be considered.I need a summed up output for days.
Type1 can be considered as primary inst & Type2 can be considered as secondary inst

Sorry for the troubles.

normal average for type 1 is 984925.96
without above condition o/p is 2.6091/984925.96=0.0000026490316
with condition o/p is 2.6091/61037.54=0.000042745824

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-10-17 : 05:44:04

Visakh I tried this one.But I am not getting the desired o/p.What wrong am I doing here?

select
SUM(type1) AS PrimaryInstrument,
SUM(CASE WHEN type2> 0 THEN type1 ELSE 0 END)*1.0/SUM(type2) AS SecondaryInstrument
from
(
select
dayno,
max(case when type=1 then reading end) as type1,
max(case when type=2 then reading end) as type2

from @tbl
group by dayno
)t

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-10-17 : 05:54:40

Visakh it worked.I used this query.

select
SUM(type1) AS PrimaryInstrument,
SUM(type2)/SUM(CASE WHEN t.type2> 0 THEN type1 ELSE 0 END)*1.0 AS SecondaryInstrument
from
(
select
dayno,
max(case when type=1 then reading end) as type1,
max(case when type=2 then reading end) as type2

from @tbl
group by dayno
)t

Thanks for all the help & the troubles.Thank you very much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-17 : 06:15:23

Sorry didnt get that. do you mean you need sum daywise?then you should get three rows but you've shown only one?

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-10-17 : 06:29:46

quote:
Originally posted by visakh16

Sorry didnt get that. do you mean you need sum daywise?then you should get three rows but you've shown only one?

No I meant irrespective of days.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources