[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

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
0
rhservan
Asked:
rhservan
  • 6
  • 5
  • 4
2 Solutions
 
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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
 
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 6
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now