Nullif WordCount 2

[michaela18]

I have the following:


Select (1+len(NULLIF(LTRIM(CodeList),''))-len(replace(NULLIF(LTRIM(CodeList),''),' ',''))) * count (*) as 'IBM_ACS'
from history
where order in ('In process', 'Order')
and date = '20100210'
and PromoCode IN ('107', 'VW109', '118')
group by codelist


This will count the number of words in a record then multiply that by the count. Some records in the Codelist field has blanks, but my select still counts them. How can I count everything without the group by as 1 sum, and without the blanks/nulls.

 

[gmmastros]

If I understand correctly.... try this:

Select  (1+len(NULLIF(LTRIM(CodeList),''))-len(replace(NULLIF(LTRIM(CodeList),''),' ',''))) *  count (*) as 'IBM_ACS'
from history
where order in ('In process', 'Order')
and date = '20100210'
and PromoCode IN ('107', 'VW109', '118')
[!]and codelist > ''[/!]
group by codelist

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[michaela18]

This is what I want but without the group by

It is now only showing those records that are populated but showing as:


APPLE 1
PC 5

I just want it to show 6

 

[markros]

What will happen if you remove GROUP BY from your query?

PluralSight Learning Library

 

[michaela18]

if i remove group by i get this "is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause."

and if I put in Sum at the beginning of my select statement and remove the group by, it would work but it won't retrieve the right information.

 

[gmmastros]

try this:

Select  [!]Sum([/!](1+len(NULLIF(LTRIM(CodeList),''))-len(replace(NULLIF(LTRIM(CodeList),''),' ','')))[!])[/!] as 'IBM_ACS'
from history
where order in ('In process', 'Order')
and date = '20100210'
and PromoCode IN ('107', 'VW109', '118')

Notice that the COUNT is no longer necessary.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom