Can I eliminat rows to count in an output column?

SQL: Beginner
I want to eliminate counting 0 on the output column.  Would null be counted as a row if I change Else 0 to Else null.

 [FreeOfCharge] =
CASE PayCode
WHEN 9 THEN OriginalPrice
ELSE 0
END

OutPut Column (row count = 7) but I don't want any 0 counted so count result would be 3
           0
           0
           0
           0
           9  ---OriginalPrice
           11---OriginalPrice
           15---OriginalPrice
rhservanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rajkumar GsSoftware EngineerCommented:
You can filter out the records by adding this WHERE condition to your main query
... WHERE ... AND PayCode <> 9

Open in new window


This condition has the same logic that you use in CASE statement. It will avoid those record that would have 0 value for that CASE statement.

Raj
0
Rajkumar GsSoftware EngineerCommented:
Oops!
Sorry.. Condition should be
... WHERE ... AND PayCode = 9 AND OriginalPrice <> 0 

Open in new window

0
CluskittCommented:
You can do this in one of many ways. Either filter out the 0's, like Rajkumar said, or just sum the other ones. Instead of COUNT(OriginalPrice), use SUM(CASE WHEN OriginalPrice<>0 THEN 1 ELSE 0 END)
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

rhservanAuthor Commented:
Raj,

Thanks,  I know how WHERE works for filtering but is there someway to use the CASE statement I sent to perform this filter.  It is in a stored procedure.

If not please set me in the correct direction to write this in my stored procedure.  I am only a beginner by I,m trying
0
CluskittCommented:
Yes, Null would be counted as a row, unless you're using this on a join. You can, however, use your query as a subquery and filter it there.
0
Rajkumar GsSoftware EngineerCommented:
If I understood correct, try this way.

SELECT * FROM
(
  YourQuery here
) A
WHERE A.FreeOfCharge <> 0

This will filter from final result.
Raj
0
CluskittCommented:
It would be easier if you were to post the stored procedure. We could assist you better that way.
0
Rajkumar GsSoftware EngineerCommented:
The same suggestion that Cluskitt: mentioned. (using sub-query)
0
rhservanAuthor Commented:
Sorry,  I cannot upload the stored porcedure.  Privacy issue.

Here is a sample of the section I am working on.

TRUNCATE TABLE FactBalloonSales;
INSERT INTO
                          dbo.FactBalloonSales
            (
            , WorkDateKey
            , EmployeeKey
            , EquipmentKey
            , LotKey
            , CustomerKey
                                          , NoChargeAmount
SELECT       
            , [WorkDayKey] = CONVERT(BIGINT, CONVERT(VARCHAR, workday, 112))
            , [EmployeeKey]= coalesce(de.EmployeeKey,0)
            , [EquipmentKey]= 1
            , [LotLocationKey] = COALESCE(L.LotKey,0)
            , [CustomerKey] = 0
                                           , [FreeOfCharge] =
                                             CASE PayCode
                                             WHEN 9 THEN OriginalPrice
                                             ELSE 0
                                             END
                                             ,[OriginalPrice]

FROM           Server.Table (nolock)                        
WHERE      1.dateout >= '09/15/2011'

      
The stored procedure creates tables then inserts the data into the tables.
The table is then used in SSAS as a facttable.
0
rhservanAuthor Commented:
So can I keep the original CASE statement in place then use the WHERE in the stored procedure to filter   NoCharge > 0 ?  Would this work?
0
CluskittCommented:
You can try:

TRUNCATE TABLE FactBalloonSales;
INSERT INTO
                          dbo.FactBalloonSales
            (
            , WorkDateKey
            , EmployeeKey
            , EquipmentKey
            , LotKey
            , CustomerKey
                                          , NoChargeAmount
SELECT * FROM (SELECT      
            , [WorkDayKey] = CONVERT(BIGINT, CONVERT(VARCHAR, workday, 112))
            , [EmployeeKey]= coalesce(de.EmployeeKey,0)
            , [EquipmentKey]= 1
            , [LotLocationKey] = COALESCE(L.LotKey,0)
            , [CustomerKey] = 0
                                           , [FreeOfCharge] =
                                             CASE PayCode
                                             WHEN 9 THEN OriginalPrice
                                             ELSE 0
                                             END
                                             ,[OriginalPrice]

FROM           Server.Table (nolock)                        
WHERE      1.dateout >= '09/15/2011') p
WHERE FreeOfCharge <> 0
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rajkumar GsSoftware EngineerCommented:
Please correct if I understood your requirement still wrongly

Based on my previous suggestion, the SELECT query could be corrected like
SELECT * FROM
(
	SELECT       
				, [WorkDayKey] = CONVERT(BIGINT, CONVERT(VARCHAR, workday, 112))
				, [EmployeeKey]= coalesce(de.EmployeeKey,0)
				, [EquipmentKey]= 1
				, [LotLocationKey] = COALESCE(L.LotKey,0)
				, [CustomerKey] = 0
											   , [FreeOfCharge] =
												 CASE PayCode
												 WHEN 9 THEN OriginalPrice
												 ELSE 0
												 END
												 ,[OriginalPrice]

	FROM           Server.Table (nolock)                        
	WHERE      1.dateout >= '09/15/2011'
) A 
WHERE A.[OriginalPrice] <> 0

Open in new window

0
Rajkumar GsSoftware EngineerCommented:
>> So can I keep the original CASE statement in place then use the WHERE in the stored procedure to filter   NoCharge > 0 ?  Would this work? <<

yes
0
rhservanAuthor Commented:
Raj,
) A
WHERE A.[OriginalPrice] <> 0       <---------------------------This works when I replace -OriginalPrice with [NoCharge]

Cluskitt,
I had to make minor corrections to make it work.  But mostly worked correctly.

Thank you both for your prompt responses and far your hard earned superior knowledge.  Excellent
0
CluskittCommented:
Glad to help :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.