CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Dev Developer
Dev Developer

Reputation: 161

how to avoid data from one column in SQL Server

I have following query which is join of two tables as you can see From the result I want to avoid data from one column. From below result set I want to avoid Other row 

IncidentCategory             No_Incident

Near Miss                  25
Road Events               129
Service Strike                150
Other                         Null  


SELECT BT.Name AS IncidentCategory, SUM(IR.ID) AS No_Incident  
From BBTypes BT LEFT JOIN  
     IncidentReports IR  
     ON BT.ID = IR.BbType AND
        IR.WbsElementId =  23 
GROUP BY BT.Name                
ORder By BT.Name DESC

Upvotes: 0

Views: 92

Answers (3)

Iain
Iain

Reputation: 36

Try adding

where BT.Name <> 'Other'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

If you are trying to ignore the NULL row, use inner join instead of an outer join:

SELECT BT.Name AS IncidentCategory, SUM(IR.ID) AS No_Incident  
From BBTypes BT JOIN  
     IncidentReports IR  
     ON BT.ID = IR.BbType AND
        IR.WbsElementId =  23 
GROUP BY BT.Name                
ORder By BT.Name DESC;

Doing the sum of an id column seems strange. I am guessing that you really want count() instead:

SELECT BT.Name AS IncidentCategory, COUNT(IR.ID) AS No_Incident  
From BBTypes BT JOIN  
     IncidentReports IR  
     ON BT.ID = IR.BbType AND
        IR.WbsElementId =  23 
GROUP BY BT.Name                
ORder By BT.Name DESC;

Upvotes: 0

Raju Padhara
Raju Padhara

Reputation: 707

In your query you use GROUP BY so it is better to use HAVING COUNT to boost your query performance.

HAVING COUNT(IR.ID)>0 is avoid null row in No_Incident column. 

SELECT BT.Name AS IncidentCategory, SUM(IR.ID) AS No_Incident  
From BBTypes BT LEFT JOIN  
     IncidentReports IR  
     ON BT.ID = IR.BbType AND
        IR.WbsElementId =  23 
GROUP BY BT.Name       
HAVING COUNT(IR.ID)>0         
ORDER BY BT.Name DESC

Upvotes: 1

Related Questions