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.
| 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. |
|
|
|
|
|
|
SQL Query Help (Probably Pretty Easy)