How to return a single recordset for each row? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to return a single recordset for each row?

Author

Topic

sqlnovice
Starting Member

10 Posts

Posted - 2003-08-18 : 17:51:21

I'm trying to return a single recordset from the same table in which the where clause is different. Something like this, but this doesn't work.

SELECT region,
CompletedCount as
(select region, count(source) from mytable where source = 'SN001'),
Incomplete as
(select region, count(source) from mytable where source = 'SN002')

group by region

This is not what I want:

select region, source, count(source) from conv160.usbrsvp
where source in ('SN001', 'SN002')
group by region, source
order by region, source

Thanks in advance!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-18 : 17:53:26

What should the data look like? Please provide some examples.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-18 : 18:36:40

SELECT region, CompletedCount = sum(case when source = 'SN001' then 1 else 0 end), Incomplete = sum(case when source = 'SN002' then 1 else 0 end)
from mytable
group by region
order by region

Just a guess without knowing what you really want

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sqlnovice
Starting Member

10 Posts

Posted - 2003-08-19 : 09:37:37

What I'm looking for is:

Region Completed Incomplete
A 12 232
B 15 257
etc.

The above solution looks good, but it throws syntax errors. [SQL0104] Token = was not valid. Valid tokens: + - AS <IDENTIFIER>. Cause . . . . . : A syntax error was detected at token =. Token = is not a valid token.

If I substitute the equals sign for AS, it throws [SQL0104] Token ( was not valid. Valid tokens: , FROM INTO. Cause . . . . . : A syntax error was detected at token (. Token ( is not a valid token.

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-19 : 10:14:58

SELECT region, sum(case when source = 'SN001' then 1 else 0 end) 'Completed', sum(case when source = 'SN002' then 1 else 0 end) 'Incomplete'
from mytable
group by region
order by region

sqlnovice
Starting Member

10 Posts

Posted - 2003-08-19 : 10:52:17

That's a beautiful thing, elegant and intuitive once you see it. Thank you so much. Final solution was

SELECT region, sum(case when source = 'SN001' then 1 else 0 end) as Completed, sum(case when source = 'SN002' then 1 else 0 end) as Incomplete
from mytable
group by region
order by region

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources