Multiple Counts on the Output line

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)

Multiple Counts on the Output line

Author

Topic

ttran
Starting Member

23 Posts

Posted - 2006-10-12 : 15:31:11

Hi,

Could someone help me how put multiple count(*) on the same output line. Or is it possible?

I have 4 different counts:

(1)
select a.psc,a.ec,a.sx,count(*) as Full1
from stf a inner join sta b on a.id = b.id
where a.del = 0 and b.del = 0
and b.jc in ('23','42','52') and b.pt > 96
group by a.psc,a.ec,a.sx

(2)
select a.psc,a.ec,a.sx,count(*) as Part1
from stf a inner join sta b on a.id = b.id
where a.del = 0 and b.del = 0
and b.jc in ('23','42','52') and b.pt <= 96
group by a.psc,a.ec,a.sx

(3)
select a.psc,a.ec,a.sx,count(*) as Full2
from stf a inner join sta b on a.id = b.id
where a.del = 0 and b.del = 0
and b.jc not in ('23','42','52') and b.pt > 96
group by a.psc,a.ec,a.sx

(4)
select a.psc,a.ec,a.sx,count(*) as Part2
from stf a inner join sta b on a.id = b.id
where a.del = 0 and b.del = 0
and b.jc not in ('23','42','52') and b.pt <= 96
group by a.psc,a.ec,a.sx

I want the output like:

psc ec sx Full1 Part1 Full2 Part2

Thank you in advance.
ttran

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-12 : 16:14:21

[code]select a.psc,
a.ec,
a.sx,
sum(case when b.jc in ('23','42','52') and b.pt > 96 then 1 else 0 end) as Full1,
sum(case when b.jc in ('23','42','52') and b.pt <= 96 then 1 else 0 end) as Part1,
sum(case when b.jc not in ('23','42','52') and b.pt > 96 then 1 else 0 end) as Full2,
sum(case when b.jc not in ('23','42','52') and b.pt <= 96 then 1 else 0 end) as Part2
from stf a
inner join sta b on a.id = b.id and b.del = 0
where a.del = 0
group by a.psc,
a.ec,
a.sx[/code]

Peter Larsson
Helsingborg, Sweden

ttran
Starting Member

23 Posts

Posted - 2006-10-12 : 17:16:29

Awesome !!
That's exactly what I want.
Thank you. Thank you.
ttran

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-12 : 21:10:32

For more information read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources