Link to home
Start Free TrialLog in
Avatar of David Megnin
David MegninFlag for United States of America

asked on

SQL SELECT SUM(CASE WHEN [ColumnName] IS NULL, or blank problem

I'm trying to use a query, part of which is below, to create some columns to use in an Excel pivot table report.
I started with just the [Yes] and [No] columns and then added the [NA], [Blank] and [oNULL] columns.

I get sums in the Yes, No and NA columns but the Blank and oNULL columns always give me the "ELSE" value equal to the number in the Yes column plus the number in the No column times whatever I have "ESLE" set to.  Normally it would be"...THEN 1 ELSE 0 END", but for the Blank and oNULL columns I change it to 10 and 100 so I could see how many times it was falling through to "ELSE".  

So, if there is a "1" in Yes, "2" in No and "4" in NA, then there will be a "30" in Blank and a "300" in oNULL using the values below.

The problem is, that's what it's doing instead of showing the number of actual blanks and NULL entries.

I just noticed that in my "AND (RegSelServ IN (@yes, @no) ) that since I've added the [NA] column I should add @NA to that IN clause.  I don't know how to resolve the blank and null issue though.

Any ideas?
DECLARE @OneStop varchar(50);
DECLARE @CaseManagerLName varchar(50);
DECLARE @FromDate smalldatetime;
DECLARE @ToDate smalldatetime;
DECLARE @ReviewerName varchar(50);
DECLARE @ReviewType varchar(50);
DECLARE @Yes VarChar(1);
DECLARE @No VarChar(1);
DECLARE @NA VarChar(1);
DECLARE @Blank VarChar(1)
SET @OneStop = '%';
SET @CaseManagerLName = '%';
SET @FromDate = '01/01/2000';
SET @ToDate = GetDate();
SET @ReviewerName = '%';
SET @ReviewType = '%';
SET @Yes = 'Y';
SET @No = 'N';
SET @NA = 'N/A';
SET @Blank = '';
 
SELECT OneStop, 'WIA_Adult' AS [Program]
  ,    LEFT(convert(char(10), ReviewDate, 127), 7) as ReviewDate
  ,    CaseManagerLName
  ,    ReviewerName
  ,   'RegSelServ' AS Question
  ,   SUM(CASE WHEN RegSelServ LIKE @Yes THEN 1 ELSE 0 END) AS Yes
  ,   SUM(CASE WHEN RegSelServ LIKE @No THEN 1 ELSE 0 END) AS No
  ,   SUM(CASE WHEN RegSelServ LIKE @NA THEN 1 ELSE 0 END) AS NA
  ,   SUM(CASE WHEN RegSelServ = @Blank THEN 1 ELSE 10 END) AS Blank
  ,   SUM(CASE WHEN RegSelServ IS NULL THEN 1 ELSE 100 END) AS oNULL
 
FROM   WIA_Adult
WHERE   (OneStop LIKE @OneStop)
  AND (CaseManagerLName LIKE @CaseManagerLName) 
  AND (ReviewDate BETWEEN @FromDate AND @ToDate) 
  AND (ReviewerName LIKE @ReviewerName) 
  AND (ReviewType LIKE @ReviewType) 
  AND (RegSelServ IN (@yes, @no) )
GROUP BY ReviewDate, CaseManagerLName, ReviewerName, OneStop

Open in new window

Avatar of BJTurner
BJTurner

Does changing the Blank and oNull really give you the times it false through to the Else?  You still have the 1 on the When clause?  I am unsure what you are wanting here.  The Then clause will give you how many blanks and nulls.  Everything else is totalled in your other sums.  The total of all the sums should be the total of all the records in the group.
ASKER CERTIFIED SOLUTION
Avatar of arturrrro
arturrrro
Flag of Poland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of David Megnin

ASKER

BJTurner, I think you're right; the Blank and oNull were just giving me some subset after filtering in the WHERE clause as arturrrro pointed out.  Hey, I'm a beginner, that's why I have to ask.  ;-)
arturrrrro, that's a beautiful thing!  It worked great.  Now I just have to change 91 instances of
AND ([SomeField] IN (@yes, @no) )
to
 AND ([SomeField] IN (@yes, @no, @NA, @Blank)  OR RegSelServ is NULL)
Thank God for regular expressions!
Thanks a lot!
Thank you very much.  It worked like a champ.