SQL Query Help (Probably Pretty Easy)

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

SQL Server Development (2000)

SQL Query Help (Probably Pretty Easy)

Author

Topic

jplayford
Starting Member

2 Posts

Posted - 2008-12-09 : 15:19:54

I must be an idiot, because I am having difficulty pulling a query for the following:

Column 1: Group Name
Column 2: # Open Claims
Column 3: # Closed Claims
Column 4: # Total Claims

My group name is my groups table.
I need the total number of open claims assigned to that group. Claims are in the claims table. As well as the closed and total..

So I would get the correct data for open claims if I:

select count(*) as opened from claims where claim_status_id = 'O'

correct data for closed:

select count(*) as opened from claims where claim_status_id = 'C'

claims.tbl contains fk to groups.tbl so I can get the group name off of that... but I am messing up the syntax trying to pull all of these columns up at once...

ANY HELP APPRECIATED... THX!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-12-09 : 16:55:10

Please post table definitions (as create table statements) and sample data (as insert statements for the tables specified)

--
Gail Shaw
SQL Server MVP

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-09 : 17:11:09

An approach to go on:


select 
sum(case claim_status_id when 'O' then 1 else 0 end ) as open,
sum(case claim_status_id when 'C' then 1 else 0 end) as closed,
count(*) as total
from claims
group by claim_status_id

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources