Select query group by showing additional 2 cols

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 group by showing additional 2 cols

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-07-31 : 13:35:45

[code]
Declare @Sample table (Dept int, rmitem int, rmptyp varchar(1), Period int)
insert @Sample
select 310, 6,'M',1 union all
select 310, 6,'M',1 union all
select 310, 6,'I',1 union all
select 310, 6,'I',1 union all
select 310, 6,'P',1 union all
select 310, 6,'P',1 union all
select 313, 8,'M',1 union all
select 313, 8,'M',1 union all
select 313, 8,'I',1 union all
select 313, 8,'I',1 union all
select 313, 8,'P',1 union all
select 313, 8,'P',1

I need to present data with two additional summed columns(OP_COUNT, IPCOUNT)

sum(OP_COUNT), sum(IP_COUNT)
group by based
on DEPt, rmitem, period
OP_Count is based on rmptype <> 'I'
IP_count is based on rmptype = 'I'

i should get 6 columns in my query.
select dept, rmitem, rmptype, period, op_count, ip_count

data is grouped by DEPt, rmitem, period
[/code]

Thank you very much for the helpful info.

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-07-31 : 14:16:12

Hi,
You said that data is group by DEPt, rmitem, period
what about rmpTyp? Should be group by ?

First with it in group by ,


select Dept,rmItem,period,rmptyp,
	sum(case when rmptyp <> 'I' then 1 else 0 end) as OP_Count,
	sum(case when rmptyp = 'I' then 1 else 0 end) as IP_Count
from [Sample]
group by Dept,rmItem,period,rmptyp

second without the field in group by


select Dept,rmItem,period,
	sum(case when rmptyp <> 'I' then 1 else 0 end) as OP_Count,
	sum(case when rmptyp = 'I' then 1 else 0 end) as IP_Count
from [Sample]
group by Dept,rmItem,period

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-07-31 : 14:57:01

Thks a lot , rmptyp should not be part of group by.
reason, there are lot of rmptypes which become OP_COUNT when it is not <> 'I'

I will do the test and see. Thks a lot.

stepson
Aged Yak Warrior

545 Posts

Posted - 2013-08-01 : 01:06:53

with welcome

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabinWeb

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-01 : 03:04:33

[code]
SELECT Dept, rmitem ,Period,[OP] AS OPCount,[IP] AS IPCount
FROM
(
SELECT Dept , rmitem ,Period ,CASE WHEN rmptyp = 'I' THEN 'IP' ELSE 'OP' END AS rmptyp
FROM Table
)t
PIVOT(SUM(1) FOR rmptyp IN ([OP],[IP]))p
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources