Row count based on the two condition

[Senthilkumar]

Dear Team,

 

I am writing a query to count rows based on the two conditions. But I am not getting proper output.

My table is 

my query is 

select count(*) as CustomerActual, Month from sbms.customerdata WHERE EmpID='83201858' AND  NOT(VisitType = 'No Due' AND VisitDate ='')  group by Month

My condition is when the date is empty it should not count that row. But if the visit type is 'No due' it should count the row.

for example, in 2024-04  month the actual row is 5. As per the above condition, the row count is 4. Because the visit type is No due on ID 1713 and ID 1712 the date is blank. so the count is 4. 

Instead of No due, if any other is in the visit type column on id 1713, then it should not count. the total count should show on 3. 

Can anyone help me with how to do this?

[Barand]

24 minutes ago, Senthilkumar said:

My condition is when the date is empty it should not count that row. But if the visit type is 'No due' it should count the row.

try

WHERE EmpID='83201858' AND NOT (VisitType <> 'No Due' AND VisitDate ='')

 

[Senthilkumar]

Thanks for your reply. I will check it and update u 

[Barand]

NOTE: I missed out a "NOT" - I edited my reply to correct.

[mac_gyver]

OR you can use the complement -

select count(*) as CustomerActual, Month from sbms.customerdata
 WHERE EmpID='83201858' AND (VisitType = 'No Due' OR VisitDate !='')
 group by Month