CODEWITHSUNDEEP
sql
user1000744
user1000744

Reputation: 123

SQL select query excluding a value

Good Day,

I want to select a table with Average of the fields

SELECT 
    CAST(SurveyDept AS NVARCHAR(100)) as Dept_name,
    CAST(SurveySubDept AS NVARCHAR(100)) as Subdept_name
    AVG(SurveyReachability) as total_R,
    AVG(SurveyProfessionalism) as total_P,
    AVG(SurveyProactiveness) as total_Pr,
    AVG(SurveyCompetence) as total_C,
    AVG(SurveyResponse) as total_Re
FROM 
    dbo.tb_SurveyDeptInfo 
GROUP BY 
    CAST(SurveyDept AS NVARCHAR(100)), 
    CAST(SurveySubDept AS NVARCHAR(100))
ORDER BY 
    CAST(SurveyDept AS NVARCHAR(100)),
    CAST(SurveySubDept AS NVARCHAR(100))

The result looks like this:

enter image description here

What I want to do is exclude the "Sub Department Name" with value (Transfers) and value (Check & Utility Service Payments) if exists

So I added the below after the (order by):

WHERE Subdept_name NOT IN (SELECT *
                           FROM dbo.tb_SurveyDeptInfo
                           WHERE CAST(SurveySubDept AS NVARCHAR(100)) = 'Transfers'
                              OR CAST(SurveySubDept AS NVARCHAR(100)) = 'Check & Utility Service Payments')

Can you advise where is the problem, it isn't returning any records

Upvotes: 0

Views: 128

Answers (2)

Utsav
Utsav

Reputation: 8143

Try this

select * from (
SELECT CAST(SurveyDept AS NVARCHAR(100)) as Dept_name,
CAST(SurveySubDept AS NVARCHAR(100)) as Subdept_name
AVG( SurveyReachability ) as total_R,
AVG( SurveyProfessionalism ) as total_P,
AVG( SurveyProactiveness ) as total_Pr,
AVG( SurveyCompetence ) as total_C,
AVG( SurveyResponse ) as total_Re
FROM dbo.tb_SurveyDeptInfo GROUP BY CAST(SurveyDept AS NVARCHAR(100)), CAST(SurveySubDept AS NVARCHAR(100))
ORDER BY CAST(SurveyDept AS NVARCHAR(100)),CAST( SurveySubDept AS NVARCHAR(100))
) tmp
WHERE tmp.Subdept_name NOT IN (
SELECT *
FROM dbo.tb_SurveyDeptInfo
WHERE
CAST(SurveySubDept AS NVARCHAR(100)) = 'Transfers'
OR
CAST(SurveySubDept AS NVARCHAR(100)) = 'Check & Utility Service Payments' 
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Just add your WHERE clause to the entire query:

SELECT CAST(SurveyDept AS NVARCHAR(100)) as Dept_name,
       CAST(SurveySubDept AS NVARCHAR(100)) as Subdept_name
       AVG( SurveyReachability ) as total_R,
       AVG( SurveyProfessionalism ) as total_P,
       AVG( SurveyProactiveness ) as total_Pr,
       AVG( SurveyCompetence ) as total_C,
       AVG( SurveyResponse ) as total_Re
FROM dbo.tb_SurveyDeptInfo
WHERE CAST(SurveySubDept AS NVARCHAR(100))  NOT IN ('Transfers', 'Check & Utility Service Payments')
GROUP BY CAST(SurveyDept AS NVARCHAR(100)), CAST(SurveySubDept AS NVARCHAR(100))
ORDER BY CAST(SurveyDept AS NVARCHAR(100)),CAST( SurveySubDept AS NVARCHAR(100));

Your query doesn't work because you have IN (SELECT * . . ). The subquery returns more than one column, so it gets an error.

I don't understand why you are doing explicit cases to NVARCHAR(100). These seem to just clutter the query. I would try:

SELECT SurveyDept as Dept_name, SurveySubDept as Subdept_name
       AVG( SurveyReachability ) as total_R,
       AVG( SurveyProfessionalism ) as total_P,
       AVG( SurveyProactiveness ) as total_Pr,
       AVG( SurveyCompetence ) as total_C,
       AVG( SurveyResponse ) as total_Re
FROM dbo.tb_SurveyDeptInfo
WHERE SurveySubDept NOT IN ('Transfers', 'Check & Utility Service Payments')
GROUP BY SurveyDept, SurveySubDept
ORDER BY SurveyDept, SurveySubDept;

Upvotes: 2

Related Questions