Reputation: 123
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:
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
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
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