Group By - depending on boolean

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)

Group By - depending on boolean

Author

Topic

mibheenick
Starting Member

12 Posts

Posted - 2005-09-12 : 06:02:58

hi again!

how do i achieve this?
ID Status Value State
1 22 50 O
2 22 10 O
3 23 25 N
4 23 15 N

Result expected
Status Sum State
22 50 O
22 10 O
23 40 N

i need the group by to be done only on records having the state = 'N'

Live together like brothers and do business like strangers.....

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-12 : 06:08:49

so you want to group only on one state value?
use a union:

select Status, Sum, State
from table
where State <> 'N'
union all
select Status, sum(Sum), State
from table
where State <> 'N'
group by Status, State

Go with the flow & have fun! Else fight the flow

mibheenick
Starting Member

12 Posts

Posted - 2005-09-12 : 06:21:58

thanx Spirit1 i did find this solution, but i have to do this in one single select statement!

coz i do retrieve other informations as well while doing the select statement. and if i use a second Select statement it will take too much time!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-12 : 07:17:07

i don't think you can... at least i don't know of a way.

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-12 : 07:37:32

I think the query suggested by spirit1 is the effecient way than other methods

If you dont want to use Union or Union All, consider this


Select 
	distinct T1.Status, case when T2.state='N' then T2.s else T1.value end as value ,t1.state from yourTable T1
	inner join (Select status,sum(value) as s , state from yourTable group by  status, state) T2 
on T1.status=T2.status

But this is not more optimised than sprit1's

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2005-09-12 : 08:00:36

I don't know if it is more efficient but give it a try:

select Status,
	"Sum",
	case (x)
	  when 0 then 'N'
          else 'O'
	end as State
from(
select Status, sum(Value) AS "Sum", 
	case (state)
          when 'O' then id
          else 0
        end as x
from table
group by Status,
	case (state)
          when 'O' then id
          else 0
        end
) as t

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-09-12 : 08:07:03

Assuming ID is a candidate key (unique, non-null),


SELECT Status, SUM(Value) AS "Sum", MIN(State) AS State
FROM YourTable
GROUP BY Status,
  CASE WHEN State = 'O' THEN ID END

Edit: Hmm,

, sort-of.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-12 : 09:02:37

Arnold, yours is simple and perfect

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources