5 Replies - 210 Views - Last Post: 05 August 2014 - 11:59 AM

#1 Zwidar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 11-June 13

summing multiple columns with some null values

Posted 31 July 2014 - 10:42 AM

I have two tables (codetable and a gifttable). Everytime we send a mailing we put a code based on how they are segmented in that mailing and each mailing has a specific givingcode.

CodeTable
acct#, mailcode
1, july2014a
2, july2014b
3, july2014c
4, july2014a

GiftTable
acct#, givingcode, amt
1, july2014, 5.00
2, july2014, 10.00

I am trying to get a report that will show the response rate for each group of the mailing.

output desired
mailcode, count(mailcode), sum(amt), count(amt)
july2014a, 2, 5.00, 1
july2014b, 1, 10.00, 1
july2014c, 1, null, null

I tried 2 different queries that get me close, but both with slightly different problems. I went through many revisions and googling to get to this point.
I finally got it to give me the right counts of the number of people with a specific code and the right counts of number of responses. It also finally gives me the july2014c row without any gifts listed.

select codetable.mailcode, codecounting.codecount, sum(amt) as Giving, count(amt) As Responses
from codetable
left join (select mailcode, count(mailcode) as codecount
           from CodeTable 
           where mailcode like 'july2014%'
           group by mailcode) codecounting 
     on CodeTable.mailcode = codecounting.mailcode
left join GiftTable
     on codetable.acct# = gifttable.acct# and givingcode = 'july2014'
--originally I had the givingcode = 'july2014' as part of the where, but then that would not return the july2014c row, since it wouldn't meet the criteria. That's when I moved it up to the join, which helped solve most the problems.
where codetable.mailcode Like 'july2014%'
group by codetable.mailcode, codecounting.codecount


This query gives me duplicate results, the extra duplicate rows shows null values.
Results I'm getting:
mailcode, count(mailcode), sum(amt), count(amt)
july2014a, 2, null, null
july2014a, 2, 5, 1
july2014b, 1, null, null
july2014b, 1, 10, 1
july2014c, 1, null, null

How can I eliminate these extra duplicate null rows?

2nd Query I tried, similar problem of extra rows.
select mailcode, count(mailcode), sum(amt) as Giving, count(amt) As Responses
from CodeTable
left join GiftTable
     on CodeTable.acct# = GiftTable.acct# and givingcode = 'july2014'
where CodeTable.mailcode Like 'july2014%'
group by mailcode


Results:
july2014a, 1, null, null
july2014a, 1, 5, 1
july2014b, 1, 10, 1
july2014c, 1, null, null

Instead of duplicate rows of each mailcode, it's separating the nulls from the giving for each mailcode. Which isn't what I really want either. Is there an easy way to combine the count(mailcode) with this query?

Any help for either query or maybe something completely different would be appreciated.

Is This A Good Question/Topic? 0
  • +

Replies To: summing multiple columns with some null values

#2 smendoza88  Icon User is offline

  • New D.I.C Head

Reputation: 7
  • View blog
  • Posts: 38
  • Joined: 13-July 11

Re: summing multiple columns with some null values

Posted 01 August 2014 - 11:53 AM

Try using isnull on the columns your counting/summing ISNULL info
Was This Post Helpful? 0
  • +
  • -

#3 Zwidar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 11-June 13

Re: summing multiple columns with some null values

Posted 01 August 2014 - 12:19 PM

View Postsmendoza88, on 01 August 2014 - 11:53 AM, said:

Try using isnull on the columns your counting/summing ISNULL info


I did try using isnull.
ISNULL seems to be just a display replacement for a null value. When I did something like isnull(count(amt),'').
Instead of having that extra row showing mailcode, count, null, null. The extra rows were showing mailcode, count, 0, 0 instead.
The extra rows still existed, only they displayed different values instead of null.
Was This Post Helpful? 0
  • +
  • -

#4 smendoza88  Icon User is offline

  • New D.I.C Head

Reputation: 7
  • View blog
  • Posts: 38
  • Joined: 13-July 11

Re: summing multiple columns with some null values

Posted 05 August 2014 - 09:12 AM

I messed around with it for a few min on sql fiddle click on the link.

sql fiddle link

 select codetable.mailcode, count(codetable.MailCode), sum(isnull(amt, 0)) as amt, count(amt) as counts
 from  codetable left join Gifts
  on codetable.acctID = gifts.acctID and giftCode = 'july2014'
 where codetable.mailcode Like 'july2014%'
group by codetable.mailCode



Was This Post Helpful? 1
  • +
  • -

#5 Zwidar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 11-June 13

Re: summing multiple columns with some null values

Posted 05 August 2014 - 10:42 AM

View Postsmendoza88, on 05 August 2014 - 09:12 AM, said:

I messed around with it for a few min on sql fiddle click on the link.

sql fiddle link

 select codetable.mailcode, count(codetable.MailCode), sum(isnull(amt, 0)) as amt, count(amt) as counts
 from  codetable left join Gifts
  on codetable.acctID = gifts.acctID and giftCode = 'july2014'
 where codetable.mailcode Like 'july2014%'
group by codetable.mailCode





That worked perfect. I kept trying to use isnull as the last thing, such as isnull(sum(...)). For whatever reason, I never though to switch the two and have isnull convert to a 0 first, in order to add 0 instead of null. Looking at it now, your way makes perfect sense. I knew I had to be overcomplicating the query, I just didn't know how I was.

Thanks!
Was This Post Helpful? 0
  • +
  • -

#6 smendoza88  Icon User is offline

  • New D.I.C Head

Reputation: 7
  • View blog
  • Posts: 38
  • Joined: 13-July 11

Re: summing multiple columns with some null values

Posted 05 August 2014 - 11:59 AM

I over complicate things all the time, that's when I walk away and come back to it later

I left the ISNULL out for the count(amt) if you do isnull and set it to zero you will get 1 as a count.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1