Stored procedure to create a new table

jessicajasper · July 9, 2018, 7:56pm

I am looking to create a stored procedure to create a new table from a dump table. In the table is ID, ResidentID, Village and answers to a survey. I would like the new table to be created with the averages for each question but only for specific answers. What I would like to do is get the average for each village for each question that has a 3 or 4 answered for each question but then divide by the number of surveys for that specific village.

E.g.
If AT has 34 surveys answered and 7 answered 4 for I am happy and 8 answered 3 for I am happy. I want to do 7+8/34 and put that into the table for AT.

Thanks

r937 · July 9, 2018, 10:08pm

please do a DESCRIBE or SHOW CREATE TABLE for your dump table

jessicajasper · July 10, 2018, 12:51pm

TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_PRECISION_RADIX	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_CATALOG	CHARACTER_SET_SCHEMA	CHARACTER_SET_NAME	COLLATION_CATALOG	COLLATION_SCHEMA	COLLATION_NAME	DOMAIN_CATALOG	DOMAIN_SCHEMA	DOMAIN_NAME
ST_QoL	dbo	Resident_Survey	SurveyID	1	(newid())	YES	uniqueidentifier	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	DateofBirth	2	NULL	YES	date	NULL	NULL	NULL	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	HealthCardNumber	3	NULL	YES	varchar	50	50	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SelectVillage	4	NULL	YES	varchar	50	50	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SEngaged	5	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SFood	6	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SControl	7	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SAttentive	8	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SCaring	9	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CanbealonewhenIwish	10	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SCanbealonewhenIwish	11	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CCanbealonewhenIwish	12	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Privacyisrespected	13	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SPrivacyisrespected	14	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CPrivacyisrepected	15	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Igetmyfavoritefoodshere	16	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIgetmyfavoritefoodshere	17	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIgetmyfavoritefoodshere	18	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	IcaneatwhenIwant	19	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIcaneatwhenIwant	20	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIcaneatwhenIwant	21	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Haveenoughvarietyinmeals	22	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SHaveenoughvarietyinmeals	23	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CHaveenoughvarietyinmeals	24	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Enjoymealtimes	25	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SEnjoymealtimes	26	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CEnjoymealtimes	27	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Foodisrighttemperature	28	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SFoodisrighttemperature	29	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CFoodisrighttemperature	30	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	IfIneedhelprightawayIcangetit	31	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIfIneedhelprightawayIcangetit	32	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIfIneedhelprightawayIcangetit	33	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Poesessionaresecure	34	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SPosessionsaresecure	35	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CPosessionsaresecure	36	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Feelsafewhenalone	37	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SFeelsafewhenalone	38	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CFeelsafewhenalone	39	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	GettheservicesIneed	40	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SGettheservicesIneed	41	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CGetthesericesIneed	42	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	WouldrecommendtheVillage	43	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SWouldrecommendtheVillage	44	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CWouldrecommendtheVillage	45	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Thisplacefeelslikehome	46	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SThisplacefeelslikehome	47	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CThisplacefeelslikehome	48	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CaneasilygooutdoorsifIwant	49	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SCaneasilygooutdoorsifIwant	50	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CCaneasilygooutdoorsifIwant	51	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Iambotheredbythenoise	52	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIambotheredbythenoise	53	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIambotheredbythenoise	54	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CanhaveabathshowerasoftenasIwant	55	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SCanhaveabathshowerasoftenasIwant	56	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CCanhaveabathshowerasoftenasIwant	57	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Idecidewhentogetup	58	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIdecidedwhentogetup	59	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIdecidewhentogetup	60	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Idecidewhentogotobed	61	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIdecidewhentogotobed	62	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIdecidewhentogotobed	63	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	IcangowhereIwantonthespurofthemoment	64	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIcangowhereIwantonthespurofthemoment	65	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIcangowhereIwantonthespurofthemoment	66	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Icontrolwhocomesintomyroom	67	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIncontrolwhocomesintomyroom	68	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIcontrolwhocomesintomyroom	69	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Idecidewhatclothestowear	70	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIdecidewhatclothestowear	71	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIdecidewhatclothestowear	72	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Idecidehowtospendmytime	73	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIdecidehowtospendmytime	74	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIdecidehowtospendmytime	75	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Iamtreatedwithrepectbystaff	76	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIamtreatediwthrespectbystaff	77	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIamtreatediwthrespectbystaff	78	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Staffpayattentiontome	79	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SStaffpayattentiontome	80	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CStaffpayattentiontome	81	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Icanexpressmyopinionwithoutfearofconsequences	82	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIcanexpressmyopinionwithoutfearofconsquences	83	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIcanexpressmyopinionwithoutfearofconsequences	84	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	StaffrespectwhatIlikeanddislike	85	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SStaffrespectiwthatIlikeanddislike	86	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CStaffrepectwhatIlikeanddislike	87	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	ThecareandsupportIgethelpemelivemylifetheyIwant	88	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SThecareandsupportIgethelpmelivemylifethewayIwant	89	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CThecareandsupportIgethelpmelivemylifethewayIwant	90	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	StaffrespondquicklywhenIaskforassistance	91	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SStaffrespondquicklywhenIaskforassistance	92	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CStaffrespondquicklywhenIaskforassistance	93	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Staffrespondtomysuggestions	94	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SStaffrespondtomysuggestions	95	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CStaffrespondtomysuggestions	96	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	IgetthehealthservicesIneed	97	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIgetthehealthservicesIneed	98	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIgetthehealthservicesIneed	99	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Staffhaveenoughtimeforme	100	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SStaffhaveenoughtimeforme	101	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CStaffhaveenoughtimeforme	102	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Staffknowwhattheyaredoing	103	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SStaffknowwhattheyaredoing	104	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CStaffknowwhattheyaredoing	105	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	MyservicesaredeliveredwhenIwantthem	106	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SMyservicesaredelievedwhenIwantthem	107	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CMyservicesaredelieveredwhenIwantthem	108	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Somestaffknowthestoryofmylife	109	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SSomestaffknowthestoryofmylife	110	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CSomestaffknowtheystoryofmylife	111	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Iconsiderastaffmembermyfriend	112	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIconsiderastaffmembermyfriend	113	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIconsiderastaffmembermyfriend	114	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Ihaveaspecialrelationshipwithastaffmember	115	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIhaveaspecialrelationshipwithastaffmember	116	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIhaveaspecialrelationshipwithastaffmember	117	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Stafftakethetimetohaveafriendlyconversationwithme	118	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SStafftakethetimetohaveafriendlyconversationwithme	119	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CStafftakethetimetohaveafriendlyconversationwithme	120	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Staffaskhowmyneedscanbemet	121	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SStaffaskhowmyneedscanbemet	122	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CStaffaskhowmyneedscanbemet	123	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Ihavethesamenurseassistmostweekdays	124	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIhavethesamenurseassitmostweekdays	125	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIhavethesamenurseassistmostweekdays	126	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Ihavenjoyablethingstodohereonweekends	127	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIhaveenjoyablethingstodohereonweekends	128	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIhaveenjoyablethingstodohereonweekends	129	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Ihaveenjoyablethingstodohereinevenings	130	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIhaveenjoyablethingstodohereinevenings	131	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIhaveenjoyablethingstodohereinevenings	132	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Iparticipateinmeaningfullactivities	133	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIparticipateinmeaningfulactivites	134	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIparticipateinmeaningfulactivities	135	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	IfIwantIcanparticipateinreligiousactivitiesthatmeaningtome	136	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIfIwantIcanparticipateinreligiousactivitiesthathavemeaningtome	137	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIfIwantIcanparticipateinreligiousactivitiesthathavemeaningtome	138	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Ihaveopportunitiestospendtimewithlikemindedresidents	139	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIhaveopportunitiestospendtimewithlikemindedresidents	140	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIhaveopportunitiestospendtimewithlikemindedresidents	141	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Ihavetheopportunitytoexplorenewskilsandinterests	142	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIhavetheopportunitytoexplorenewskillsandinterests	143	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIhavetheopporunitytoexplorenewskillsandinterests	144	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Anotherfriendhereismyclosefriend	145	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SAnotherfriendhereismyclosefriend	146	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CAnotherfriendhereismyfriend	147	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Peopleaskformyhelporadvice	148	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SPeopleaskformyhelporadvice	149	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CPeopleaskformyhelporadvice	150	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Ihaveopportunitiesforaffectionorromance	151	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIhaveopportunitiesforaffectionorromance	152	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIhaveopportunitiesforaffectionorromance	153	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Itiseasytomakefriendshere	154	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SItiseasytomakefriendshere	155	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CItiseasytomakefriendshere	156	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Ihavepopletodothingswith	157	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	SIhavepeopletodothingswith	158	NULL	YES	int	NULL	NULL	10	10	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	CIhavepeopletodothingswith	159	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Partofacouple	160	NULL	YES	nvarchar	50	100	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Gender	161	NULL	YES	nvarchar	50	100	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Age	162	NULL	YES	nvarchar	50	100	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Health	163	NULL	YES	nvarchar	50	100	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	TimeatVillage	164	NULL	YES	nvarchar	50	100	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Comments	165	NULL	YES	varchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Status	166	NULL	YES	varchar	50	50	NULL	NULL	NULL	NULL	NULL	NULL	iso_1	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Created	167	NULL	YES	date	NULL	NULL	NULL	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Lastupdated	168	NULL	YES	date	NULL	NULL	NULL	NULL	NULL	0	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Setting	169	NULL	YES	nvarchar	50	100	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Village	170	NULL	YES	nvarchar	50	100	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL
ST_QoL	dbo	Resident_Survey	Neighbourhood	171	NULL	YES	nvarchar	-1	-1	NULL	NULL	NULL	NULL	NULL	NULL	UNICODE	NULL	NULL	SQL_Latin1_General_CP1_CI_AS	NULL	NULL	NULL

r937 · July 10, 2018, 3:11pm

could you point out which columns contain the data that you want averaged, and also, which specific answers to test for

jessicajasper · July 11, 2018, 12:51pm

So I have gotten a bit closer to what I want trying to achieve but it is still not doing what I want it do. This is what I have come up with but I don’t know how to get to divide by the sum. SELECT (SELECT COUNT() FROM Resident_Survey WHERE CanbealonewhenIwish = 3 and Village = ‘WP’ and Setting = ‘LTC’)+ (SELECT COUNT() FROM Resident_Survey WHERE CanbealonewhenIwish = 4 and Village = ‘WP’ and Setting = ‘LTC’)/ (SELECT COUNT(*) FROM Resident_Survey WHERE Village = ‘WP’ and Setting = ‘LTC’) AS ICanbealonewhenIwish

r937 · July 11, 2018, 2:30pm

try this –

SELECT 100.0 * COUNT( CASE WHEN CanbealonewhenIwish IN ( 3,4 ) THEN 1 ELSE NULL END) / COUNT(*) AS percent FROM Resident_Survey WHERE Village = 'WP' and Setting = 'LTC'

jessicajasper · July 11, 2018, 4:11pm

AHH! Thank you so much that was exactly what I was looking for.

rpkamp · July 11, 2018, 7:00pm

<offtopic>

Just wondering, @r937, I think I’d use SUM() with 1 and 0 instead of COUNT() with 1 and NULL like so:

SUM( CASE WHEN CanbealonewhenIwish IN ( 3,4 )
          THEN 1
          ELSE 0 END)

mostly because I didn’t know that COUNT(NULL) is 0 , and also because it somehow makes more sense in my mind.

Is there any significant difference between the two, or are the fully interchangable?

r937 · July 11, 2018, 7:38pm

i think it’s a wash

at least i didn’t use ‘Humpty’ this time (because there was no need to have a ‘Dumpty’ as well)

system · October 11, 2018, 2:38am

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.