counting nulls and blank fields together 3

[FALCONSEYE]

i have a table as follows:

campaignName status occurence
yahoo null 5
yahoo 4
someOther 1:Vfd 6
...

the status on the second row is blank. how can i do count(1) whenever the status is null or blank for the same campaignName? for example,when i query the table for the above example i want to display

campaign status
yahoo 9 approved
someOther 6 1:Vfd

thanks for the help in advance

http://www.jetansolutions.com

 

[bborissov]

SELECT SUM(
           CASE Status
                WHEN IS NULL 1
                WHEN '' 1
                ELSE 0 END) AS mmm

Something like that (not tested)

Borislav Borissov

 

[DotNetGnat]

something along these lines...

SUM (CASE WHEN status is null OR status='' then occurence else 0 end) as mytotal

-DNG

 

[SQLBill]

Won't this work?

SELECT CampaignName AS [Compaign],
       COUNT(Occurance),
       Status
GROUP BY CampaignName, Status
FROM tablename

If that returns the values you want, then all you have to do is change it to do the concatenation of the count and status.

-SQLBill

Posting advice: FAQ481-4875

 

[FALCONSEYE]

Borislav, DNG that's actually what i tried and it doesn't produce the result i wanted. SQLBill, your solution is similar too. Whenever the status is null or blank that means it's approved so i want to

campaignName status occurence
yahoo null 5
yahoo 4


9 approved yahoo.

this is what i have so far

select
sum(case when status is null OR status = '' then 1
else 0 end) as errorCount,

campaignName as campaignName,

(case when status is null OR status = '' then 'approved'
else status end)
as errorMessage


from transactionlog, campaign, orders
where orders.orderid = transactionlog.orderid
and campaign.campaignid = orders.campaignid
and orders.orderdate between '10/10/2005' and '10/12/2005'
group by campaignName, status
order by campaignName desc

thanks for the help...

http://www.jetansolutions.com

 

[vongrunt]

I guess blank is semantically the same as NULL (not entered value, unknown) so...

select campaignName, isnull(status, 'approved') as status, sumocc
from
(	select campaignName, nullif(status, '') as status, sum(occurence) as sumocc
	from blah
	group by campaignName, nullif(status, '')
) somename
-- order by campaignName

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[FALCONSEYE]

vongrunt u are right. null and blank means the same. however, i don't think i understand what goes on in your query

http://www.jetansolutions.com

 

[vongrunt]

create table blah (campaignName varchar(20), status varchar(8), occurence int)

insert into blah values ('yahoo', null, 5)
insert into blah values ('yahoo', '', 4)
insert into blah values ('someOther', '1:Vfd', 6)

<query from above>

drop table blah

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[FALCONSEYE]

sweet, i will create a temp table and do the same thing. thanks

http://www.jetansolutions.com

 

[vongrunt]

Er... there is no need for temp table.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[DotNetGnat]

if one solution stands unqiue and perfect out of many suggested solutions...you can undoubtedly say that solution was from vongrunt...

star for you...

-DNG

 

[Jasen]

You should probably also find out why this field is being populated inconsistently. Either set a default value to avoid the nulls, or alter whatever procdure/app is inserting nulls or blanks to keep the data consistent. Then you wouldn't have to do these little dances for a simple select statement.
Assuming of course, you have access to make these kinds of changes.

 

[vongrunt]

I agree... long-term-wise it is better to make data consistent. Queries get simpler and you don't have to about SQL patc... erm, hacks written some 6 months ago...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[amrita418]

Not sure why an inline view is needed here?

select    campaignname, 
          case when status is null or status = '' then 'approved' else status end, 
          sum(occurence)
from      #blah
group by  campaignname, 
          case when status is null or status = '' then 'approved' else status end

Regards,
AA

 

[r937]

why doesn't anyone ever mention COALESCE?

instead of this --

... status is null OR status = ''

why doesn't anyone ever code this --

... coalesce(status,'') = ''



r937.com | rudy.ca

 

[vongrunt]

> Not sure why an inline view is needed here?

Of course it is not needed. I was trying to illustrate how NULLIF() works (hoping someone would run derived table code separately).

> why doesn't anyone ever mention COALESCE?

why doesn't anyone ever uses NULLIF? It is designed exactly to eliminate "fake NULLs". I guess difference between these two the is academic (e.g. nullif(status,'') is null)... and conventional boolean expressions (if this then that) are easier to understand.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)