CODEWITHSUNDEEP
sqlsql-server
Willie Chen
Willie Chen

Reputation: 180

How to return 0 instead of blank SQL Server

I am using the count to get the total number, but missing team 4 value.

Here is my query:

select 
    [Team Number], Count([Followup]) as [FeedbackNeedToBeReview]
from  
    [TICKETFEEDBACK].[db_fdd63213_8581_4bef_ae7d_004c96c5873a].[Access].[TicketFeedback Tool]
where 
    [Followup] = ''
group by 
    [Team Number]
order by 
    CAST([Team Number] as int)

Output :

enter image description here

If I set the condition for Team 4,Nothing is returned.

select 
    [Team Number], Count([Followup]) as [FeedbackNeedToBeReview]
from  
    [TICKETFEEDBACK].[db_fdd63213_8581_4bef_ae7d_004c96c5873a].[Access].[TicketFeedback Tool]
where 
    [Followup] = ''
    and [Team Number] = '4'
group by 
    [Team Number]
order by 
    CAST([Team Number] as int)

Output :

enter image description here

how can i return TeamNumber [FeedbackNeedToBeReview] 4 0

Upvotes: 0

Views: 673

Answers (1)

Eralper
Eralper

Reputation: 6612

The correct way of this query is in my opinion as follows:

We need a table named Teams with TeamNumber field. In this table all possible teams should be existing including Team#4

Then In your query join Teams table with Feedback table. You will be using LEFT JOIN to feedbacks table

This will ensure that all rows from Teams including Team number 4 will be in the output with no feedbacks so 0 will be counted

An other option is simply using a SQL CTE expression to simulate this as follows

declare @team int = 4
;with teams_cte as (
    select @team teamnumber
)
select 
    teams_cte.teamnumber, COUNT(Followup) snt
from teams_cte
left join feedbacks on feedbacks.teamnumber = teams_cte.teamnumber
where teams_cte.teamnumber = 4
group by teams_cte.teamnumber

which returns 4-0 as desired

Upvotes: 4

Related Questions