return values after group by - 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 2000 Forums

Transact-SQL (2000)

return values after group by

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-07-08 : 17:21:47

If I run the following query

Select ‘status’ as status
, Isnull(sum(case p_type when ‘0’ then 1 else 0 end),0) as account
, Isnull(sum(case p_type when ‘1’ then 1 else 0 end),0) as holder

from dev

where O_type = ‘A’

it return all 0 because there are no record that match O_type = ‘A’
The result looks like:


status        account   holder
-------       ---------   --------
status        0               0

However, if I add org_id and group by org_id. It return nothing

Select org. org_id, ‘status’ as status ,
, Isnull(sum(case p_type when ‘0’ then 1 else 0 end),0) as account
, Isnull(sum(case p_type when ‘1’ then 1 else 0 end),0) as holder

from dev
inner join org on dev.org_id = org.org_id

where O_type = ‘A’
group by org_id


status     account   holder
-------     ---------   --------

How can I return values even though there are no matching records after group by org_id?

The result should looks like:


   org_id    status           account      holder
   ---------  --------        --------      -------
  1200        status           0                  0
  1201        status           0                  0
  1202        status           0                  0
  1203        status           0                  0

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-07-08 : 21:31:46

I tried group by all to retrive the org_id witch does not include any rows that meet the qualification specified in the WHERE clause.
but, it giving me an error:
GROUP BY ALL is not supported in queries that access remote tables if there is also a WHERE clause in the query.

Is there any way that I can use group by all in the distributed query( I am pulling the data from a linked server)?

or other alternative way that I can retrive the group that doesn't have any rows that meet the qualification specified in the WHERE clause?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-07-09 : 04:28:23

FULL OUTER JOIN maybe...

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-07-09 : 05:02:16

Oh BTW

THE agregation inside the isnull function is pointless.
You are still going to have the warning: Warning: Null value is eliminated by an aggregate or other SET operation. returned.

Put the isnull inside the aggregation:

--*******************************************
create table #test99(grpby varchar(10),
aggrval INT);

INSERT INTO #test99 VALUES('A', 10)
INSERT INTO #test99 VALUES('B', 20)
INSERT INTO #test99 VALUES('C', 30)
INSERT INTO #test99 VALUES('A', 20)
INSERT INTO #test99 VALUES('A', 10)
INSERT INTO #test99 VALUES('B', 10)
INSERT INTO #test99 VALUES('B', 20)
INSERT INTO #test99 VALUES('A', 20)
INSERT INTO #test99 VALUES('A', 20)
INSERT INTO #test99 VALUES('C', 30)
INSERT INTO #test99 VALUES('D', 40)
INSERT INTO #test99 VALUES('A', 50)
INSERT INTO #test99 VALUES('D', 10)
INSERT INTO #test99 VALUES('E', 10)
INSERT INTO #test99 VALUES('F', 30)
INSERT INTO #test99 VALUES('G', 10)
INSERT INTO #test99 VALUES('A', 10)
INSERT INTO #test99 VALUES('A', 10)
INSERT INTO #test99 VALUES('A', NULL)
INSERT INTO #test99 VALUES('B', NULL)
INSERT INTO #test99 VALUES('C', 130)
INSERT INTO #test99 VALUES('A', 120)
INSERT INTO #test99 VALUES('D', 100)
INSERT INTO #test99 VALUES('B', NULL)
INSERT INTO #test99 VALUES('B', 20)
INSERT INTO #test99 VALUES('A', NULL)
INSERT INTO #test99 VALUES('A', 120)
INSERT INTO #test99 VALUES('C', 230)
INSERT INTO #test99 VALUES('D', 340)
INSERT INTO #test99 VALUES('A', 450)
INSERT INTO #test99 VALUES('D', 510)
INSERT INTO #test99 VALUES('E', 610)
INSERT INTO #test99 VALUES('F', 730)
INSERT INTO #test99 VALUES('G', 810)
INSERT INTO #test99 VALUES('A', NULL)
INSERT INTO #test99 VALUES('A', NULL)

select grpby, isnull(sum(aggrval), 0)
from #test99
group by grpby

select grpby, sum(isnull(aggrval, 0))
from #test99
group by grpby

drop table #test99

--*********************************************

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources