select query sum columns two based on type

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 2008 Forums

Transact-SQL (2008)

select query sum columns two based on type

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-08-03 : 08:06:44

Is it possible I want to use the below query with two sum columns which need to be based on rmtyp, both values to appear in a row as IP_Act_cnt and OP_Act_CNT, RMTYP is not part of group by clause.

activity_count is an int column.



select RMHSP#,RMYR,RMDEPT,RMITEM,PERIOD,
(sum(activity_count) case when RMTYP='I') as IP_ACT_CNT,
(sum(activity_count) case when RMTYP='O') as OP_ACT_CNT
from [ACT_MTHLY_REV]
where RMDEPT=310 and RMITEM=1 and period=1
group by RMHSP#,RMYR,RMDEPT,RMITEM,PERIOD

Thank you very much for the helpful info.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-08-03 : 08:33:35

[code]
select RMHSP#,RMYR,RMDEPT,RMITEM,PERIOD,
(sum(case when RMTYP='I' then activity_count end)) as IP_ACT_CNT,
(sum(case when RMTYP='O' then activity_count end)) as OP_ACT_CNT
from [ACT_MTHLY_REV]
where RMDEPT=310 and RMITEM=1 and period=1
group by RMHSP#,RMYR,RMDEPT,RMITEM,PERIOD[/code]

KH
[spoiler]Time is always against us[/spoiler]

sivadss2007
Starting Member

18 Posts

Posted - 2013-08-28 : 10:59:20

select RMHSP#,RMYR,RMDEPT,RMITEM,PERIOD,
( case when RMTYP='I' then sum(activity_count)) as IP_ACT_CNT,
( case when RMTYP='O' then sum(activity_count)) as OP_ACT_CNT
from [ACT_MTHLY_REV]
where RMDEPT=310 and RMITEM=1 and period=1
group by RMHSP#,RMYR,RMDEPT,RMITEM,PERIOD

P.Siva

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources