If then statement to show <5

[jessicajasper]

Hello,

I am using SQL Server 2016. I have created an query to calculate the number of times a number appears and for each neighbourhood and village. I have also added a count for the number of survey’s completed for each neighbourhood. Now because this is anonymous survey for neighbourhoods that are less <5 I would like the value to show <5 instead of the number of completed surveys. Once this is complete I would like to change the number in columns for Never, Rarely, Sometimes etc… to have a dash since we can’t display the number of residents who contributed to that survey. I really hopes this makes sense.

Here is the code I am using:

INSERT INTO Responses
	(Village
	, Neighbourhood
	, Question
	, Never
	, Rarely
	, Sometimes
	, Mostofthetime
	, Always
	, DontKnow
	, Refused
	, NoResponse
	, NumberCompleted
	, Year)
SELECT CONCAT (Village,Setting) as Village
	, Neighbourhood
	, 'I can be alone when I wish' as Question
	, COUNT(CASE WHEN CanbealonewhenIwish IN (0.00)
			THEN 1
			ELSE NULL END) AS Never
	, COUNT (CASE WHEN CanbealonewhenIwish IN (1.00)
			THEN 1
			ELSE NULL END) AS Rarely
	, COUNT(CASE WHEN CanbealonewhenIwish IN (2.00)
			THEN 1
			ELSE NULL END) AS Sometimes
	, COUNT(CASE WHEN CanbealonewhenIwish IN (3.00)
			THEN 1
			ELSE NULL END) AS Mostofthetime
	, COUNT(CASE WHEN CanbealonewhenIwish IN (4.00)
			THEN 1
			ELSE NULL END) AS Always
	, COUNT(CASE WHEN CanbealonewhenIwish IN (6.00)
			THEN 1
			ELSE NULL END) AS DontKnow
	, COUNT(CASE WHEN CanbealonewhenIwish IN (7.00)
			THEN 1
			ELSE NULL END) AS Refused
	, COUNT(CASE WHEN CanbealonewhenIwish IN (8.00)
			THEN 1
			ELSE NULL END) AS NoResponse
	, COUNT(CanbealonewhenIwish) AS NumberCompleted
	, Year
FROM Resident_Survey
GROUP BY Village, Neighbourhood, Setting, Year

I understand I want to use a case statement I am just unsure of how to create the case statement.

And here is an example of what I would:

Domain           Completed Survey     Never      Rarely        Sometimes
Privacy                  52             15         18              20
Tecumseh                 <5             --         --              --

Thanks

Jes

[r937]

so you decided not to use PIVOT?