Case When Count? - Dev Shed

Dev Shed Forums Sponsor:

Be the architects of evolution and help create the mobile internet future. It’s your move---enter to win here!

February 15th, 2006, 09:53 AM

Cheesefood

Mmmm...Donkey punch...

Join Date: Jun 2004

Location: All up in your grill

Posts: 978

Time spent in forums: 5 Days 10 h 7 m 13 sec
Reputation Power: 37

Case When Count?

Here's what I want my query to do, can someone help me translate it to the right context?

Code:

CASE WHEN (count(a.loan_acct) > 1) then (count(a.loan_acct)) end) as more

It doesn't like the COUNT statement in the CASE. I'm running SQL over a program called QMF on a DB2 server.

__________________
I'm a geek and not 31337.

My CafePress Store. Buy a Shirt and Support Me.

February 15th, 2006, 10:06 AM

r937

SQL Consultant

Dev Shed God 25th Plane (17000 - 17499 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,344

Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 30 m 33 sec
Reputation Power: 891

Quote:

Originally Posted by Cheesefood

It doesn't like the COUNT statement in the CASE.

could you please show the entire query?

maybe you forgot your GROUP BY clause

Comments on this post

Cheesefood agrees: For being nice and helping.

__________________
r937.com | rudy.ca

February 15th, 2006, 11:09 AM

Cheesefood

Mmmm...Donkey punch...

Join Date: Jun 2004

Location: All up in your grill

Posts: 978

Time spent in forums: 5 Days 10 h 7 m 13 sec
Reputation Power: 37

Quote:

Originally Posted by r937

could you please show the entire query?

maybe you forgot your GROUP BY clause

OK, here it is with stuff changed for security purposes:

Code:

SELECT DISTINCT

A.BAGT_NUM, 
B.B_LEG_NAME,
SUM(case when c.pins_num = '75530' then (c.p_prem) end) as PB_TOTAL,
count(case when c.pins_num = '75530' then (a.ln_acct) end) as PB_Contracts,
SUM(CASE WHEN  (c.pins_num != '75530' and  c.pins_num != '97000') THEN ( c.p_prem) END) AS NON_PB_TOTAL,
(Count(case when (a.ORIG_DT >= '01-01-2005' AND a.ORIG_DT  <= '12-31-2005' and c.pins_num != '75530' and  c.pins_num != '97000') then (a.ln_acct) end)) as Non_PB_Contracts,
SUM( c.p_prem)  as ALL_BIZ,
count (case when (a.ORIG_DT >= '01-01-2005' AND a.ORIG_DT  <= '12-31-2005' and c.pins_num != '97000' ) then (a.cust_ln1) end) as Total_Contracts,

FROM a
inner  JOIN b
ON a.bagt_num = b.bagt_num 
and a.c_num = b.c_num 
inner JOIN C
on (A.p_ref = c.p_loan_ref)

WHERE
(a.ORIG_DT>='01-01-2005' AND a.ORIG_DT<= '12-31-2005')
GROUP BY a.bagt_num, b.ba_leg_name
ORDER BY PB_TOTAL, a.bagt_num

February 15th, 2006, 11:18 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,344

Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 30 m 33 sec
Reputation Power: 891

Code:

SELECT A.BAGT_NUM
     , B.B_LEG_NAME
     , SUM(      case when c.pins_num = '75530' 
                      then c.p_prem end            ) as PB_TOTAL
     , COUNT(    case when c.pins_num = '75530' 
                      then a.ln_acct end           ) as PB_Contracts
     , SUM(      CASE WHEN c.pins_num != '75530' 
                       and c.pins_num != '97000'
                      THEN c.p_prem END            ) AS NON_PB_TOTAL
     , Count(    case when a.ORIG_DT >= '01-01-2005' 
                       AND a.ORIG_DT <= '12-31-2005' 
                       and c.pins_num != '75530' 
                       and c.pins_num != '97000'
                      then a.ln_acct end           ) as Non_PB_Contracts
     , SUM(c.p_prem)  as ALL_BIZ
     , count(    case when a.ORIG_DT >= '01-01-2005' 
                       AND a.ORIG_DT <= '12-31-2005' 
                       and c.pins_num != '97000'  
                      then a.cust_ln1 end          ) as Total_Contracts
     , case when count(a.ln_acct) > 1
            then count(a.ln_acct)
            else NULL end                   as overs

FROM ...

are you sure you are want COUNT in columns 4, 6, and 8?

February 15th, 2006, 11:39 AM

Cheesefood

Mmmm...Donkey punch...

Join Date: Jun 2004

Location: All up in your grill

Posts: 978

Time spent in forums: 5 Days 10 h 7 m 13 sec
Reputation Power: 37

Quote:

Originally Posted by r937

Code:

SELECT A.BAGT_NUM
     , B.B_LEG_NAME
     , SUM(      case when c.pins_num = '75530' 
                      then c.p_prem end            ) as PB_TOTAL
     , COUNT(    case when c.pins_num = '75530' 
                      then a.ln_acct end           ) as PB_Contracts
     , SUM(      CASE WHEN c.pins_num != '75530' 
                       and c.pins_num != '97000'
                      THEN c.p_prem END            ) AS NON_PB_TOTAL
     , Count(    case when a.ORIG_DT >= '01-01-2005' 
                       AND a.ORIG_DT <= '12-31-2005' 
                       and c.pins_num != '75530' 
                       and c.pins_num != '97000'
                      then a.ln_acct end           ) as Non_PB_Contracts
     , SUM(c.p_prem)  as ALL_BIZ
     , count(    case when a.ORIG_DT >= '01-01-2005' 
                       AND a.ORIG_DT <= '12-31-2005' 
                       and c.pins_num != '97000'  
                      then a.cust_ln1 end          ) as Total_Contracts
     , case when count(a.ln_acct) > 1
            then count(a.ln_acct)
            else NULL end                   as overs

FROM ...

are you sure you are want COUNT in columns 4, 6, and 8?

Yes. I'm looking to get the total amount of contracts (dollar amount) and then the number of contracts.

Basically, I'm setting it up like this: agents can give us PB business or non-PB business. We want to find all PB business and compare it to the non-PB business to see where we're getting more PB than non-PB.

Right now, the results I'm getting are off. If I run a more specialized query, I can find that there are 1 PB contract and 84 non-PB contracts for a particular agent.

If I remove the ln_acct numbers from the select (so that all contracts are lumped to one line), I get 1 PB and 91 non-PB contracts (since several of the policies have 2 loans). Right now, the "overs" column is WAY off, showing 123 contracts.

Last edited by Cheesefood : February 15th, 2006 at 11:42 AM.

February 15th, 2006, 11:49 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,344

Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 30 m 33 sec
Reputation Power: 891

in that case i would check your joins

the reason i asked about the COUNTs in columns 4,6,8 is because you are counting column values and maybe some of them are null

most people would write , SUM( case ... then 1 else 0 end ) as foo_count

February 15th, 2006, 12:46 PM

Cheesefood

Mmmm...Donkey punch...

Join Date: Jun 2004

Location: All up in your grill

Posts: 978

Time spent in forums: 5 Days 10 h 7 m 13 sec
Reputation Power: 37

OK, I changed it to SUM..1..0 but it's still not counting multiple values as only 1. Your suggestion is probably a great lesson, but for now it's giving me the same results.

February 15th, 2006, 12:50 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,344

Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 30 m 33 sec
Reputation Power: 891

Quote:

Originally Posted by Cheesefood

OK, I changed it to SUM..1..0 but it's still not counting multiple values as only 1.

then it's a data problem with your joins

you have too many rows matching

February 15th, 2006, 01:01 PM

Cheesefood

Mmmm...Donkey punch...

Join Date: Jun 2004

Location: All up in your grill

Posts: 978

Time spent in forums: 5 Days 10 h 7 m 13 sec
Reputation Power: 37

Quote:

Originally Posted by r937

then it's a data problem with your joins

you have too many rows matching

No, I'm pretty sure it's just a statement that I can't come up with. If two rows looked like this:

ln_acct | p_prem | bagt_num | co_name |
11111 1234.55 08191 Robbs House
11111 23456.78 08191 r937 Manner

I want the p_prems added together, but then count it as only one row.

This leads me back to my original post. I want to make an IF...THEN that says IF there are more than one instance of LN_ACCT, then subtract the extra instance. It's just not possible to do a CASE statement that has a column function like " when COUNT(ln_acct) > 1"

Last edited by Cheesefood : February 15th, 2006 at 01:05 PM.

#10

February 15th, 2006, 01:54 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,344

Time spent in forums: 1 Month 3 Weeks 5 Days 8 h 30 m 33 sec
Reputation Power: 891

Quote:

Originally Posted by Cheesefood

I want the p_prems added together, but then count it as only one row.

in that case, you need another level of grouping, don't you

#11

February 15th, 2006, 03:35 PM

Cheesefood

Mmmm...Donkey punch...

Join Date: Jun 2004

Location: All up in your grill

Posts: 978

Time spent in forums: 5 Days 10 h 7 m 13 sec
Reputation Power: 37

Figured it out using a different field.

Thanks for the help!

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode