CODEWITHSUNDEEP
sqlsql-servert-sql
Vik
Vik

Reputation: 89

SQL QUERY case with a condition

I have a sql query where I have to get records count, I am getting counts correctly with the current query but I want to only get counts for records where 'Unchecked Count' is not zero.

   SELECT        
    dbo.Customer.AccountNo AS Cust_Acc_No, 
    dbo.Customer.Name AS [Customer Name], 
    dbo.Customer.Adrs1 AS Cust_Address_1,    
    dbo.Customer.Adrs2 AS Cust_Address_2, 
    dbo.Customer.City AS Cust_City, 
    dbo.Customer.Province, 
    dbo.Customer.PostalCode AS Cust_Postal_Code, 
    dbo.Customer.Email1 AS Email, 
    CAST(dbo.Customer.AccStatus AS int) AS [Account Status], 
    dbo.Customer.ID AS CID, 
    case when  COUNT(dbo.Manifest.ID) <  
         sum( CASE WHEN (dbo.Manifest.CheckedBy IS NULL OR
            (LTRIM(RTRIM(dbo.Manifest.CheckedBy)) = '')) THEN 1 ELSE 0 END) 
    then  COUNT(dbo.Manifest.ID) else
         sum( CASE WHEN (dbo.Manifest.CheckedBy IS NULL OR
         (LTRIM(RTRIM(dbo.Manifest.CheckedBy)) = '')) THEN 1 ELSE 0 END) 
    end as [Unchecked Count],
    case when  COUNT(dbo.Manifest.ID) <  
         sum( CASE WHEN (dbo.Manifest.CheckedBy IS not NULL OR
            (LTRIM(RTRIM(dbo.Manifest.CheckedBy))! = '')) THEN 1 ELSE 0 END) 
    then  COUNT(dbo.Manifest.ID) else
         sum( CASE WHEN (dbo.Manifest.CheckedBy IS not NULL OR
            (LTRIM(RTRIM(dbo.Manifest.CheckedBy))! = '')) THEN 1 ELSE 0 END) 
    end as [Checked_Count],
    COUNT(dbo.Manifest.ID) as Total

   FROM            
    dbo.Customer inner JOIN dbo.Manifest ON dbo.Customer.AccountNo = dbo.Manifest.FKAccountNo

   GROUP BY 
    dbo.Customer.AccountNo, 
    dbo.Customer.Name, 
    dbo.Customer.Adrs1, 
    dbo.Customer.City, 
    dbo.Customer.Province, 
    dbo.Customer.Adrs2, 
    dbo.Customer.Email1, 
    dbo.Customer.PostalCode, 
    dbo.Customer.AccStatus, 
    dbo.Customer.ID,Manifest.FKAccountNo

Upvotes: 1

Views: 114

Answers (3)

AlexanderW
AlexanderW

Reputation: 147

You can query the result of the other query. SELECT * FROM ( <your query> ) AS T WHERE [Unchecked Count] > 0

Upvotes: 1

Vik
Vik

Reputation: 89

Thank you AlexanderW and Shakti for your answers.I tried both of your suggestions but 'Unchecked Count' is a alias, that is why I was getting 'invalid Column error'. But your answers helped me to solve the problem. Here is how it is working now, please let me know if you have better suggestion/s.. 

WITH OnlyUnchecked AS(<MyQuery>)
SELECT * 
FROM OnlyUnchecked 
WHERE [Unchecked Count]>0

Upvotes: 0

Shakti
Shakti

Reputation: 189

Try Having clause after your group by clause

having [Unchecked Count] > 0

Full query 

SELECT        dbo.Customer.AccountNo AS Cust_Acc_No, dbo.Customer.Name AS [Customer Name], dbo.Customer.Adrs1 AS Cust_Address_1, dbo.Customer.Adrs2 AS Cust_Address_2, dbo.Customer.City AS Cust_City, 
                     dbo.Customer.Province, dbo.Customer.PostalCode AS Cust_Postal_Code, dbo.Customer.Email1 AS Email, CAST(dbo.Customer.AccStatus AS int) AS [Account Status], dbo.Customer.ID AS CID, 
                    case when  COUNT(dbo.Manifest.ID) <  sum( CASE WHEN (dbo.Manifest.CheckedBy IS NULL OR
                     (LTRIM(RTRIM(dbo.Manifest.CheckedBy)) = '')) THEN 1 ELSE 0 END) 
                     then  COUNT(dbo.Manifest.ID) else
                     sum( CASE WHEN (dbo.Manifest.CheckedBy IS NULL OR
                     (LTRIM(RTRIM(dbo.Manifest.CheckedBy)) = '')) THEN 1 ELSE 0 END) 
                     end
                     as [Unchecked Count],
                      case when  COUNT(dbo.Manifest.ID) <  sum( CASE WHEN (dbo.Manifest.CheckedBy IS not NULL OR
                     (LTRIM(RTRIM(dbo.Manifest.CheckedBy))! = '')) THEN 1 ELSE 0 END) 
                     then  COUNT(dbo.Manifest.ID) else
                     sum( CASE WHEN (dbo.Manifest.CheckedBy IS not NULL OR
                     (LTRIM(RTRIM(dbo.Manifest.CheckedBy))! = '')) THEN 1 ELSE 0 END) 
                     end
                     as [Checked_Count],
                     COUNT(dbo.Manifest.ID) as Total

                     FROM            dbo.Customer inner JOIN
                     dbo.Manifest ON dbo.Customer.AccountNo = dbo.Manifest.FKAccountNo
                     GROUP BY dbo.Customer.AccountNo, dbo.Customer.Name, dbo.Customer.Adrs1, dbo.Customer.City, dbo.Customer.Province, dbo.Customer.Adrs2, dbo.Customer.Email1, dbo.Customer.PostalCode, dbo.Customer.AccStatus, 
                     dbo.Customer.ID,Manifest.FKAccountNo
    having [Unchecked Count] > 0

Upvotes: 2

Related Questions