Using a computed column to create a table

Databases
1

Hello,

I am looking to create a stored procedure that will create a table that has calculated values. I have the equation to create the columns completed and I have created the table in the stored procedure but I am unsure how insert the values to create the table from a computed column for multiple columns. This is what I currently have:

INSERT INTO AverageTest (Village, IcanbealonewhenIwish)
SELECT Village, 100.0 *
	COUNT(CASE WHEN CanbealonewhenIwish IN (3,4)
				THEN 1
				ELSE NULL END) /
	COUNT(*) AS IcanbealonewhenIwish
FROM Resident_Survey
WHERE Village = 'WP'
	and Setting = 'LTC'
GROUP BY Village
UNION ALL
SELECT Village, 100.0 *
	COUNT(CASE WHEN CanbealonewhenIwish IN (3,4)
				THEN 1
				ELSE NULL END) /
	COUNT(*) AS IcanbealonewhenIwish
FROM Resident_Survey
WHERE Village = 'AT'
	and Setting = 'RH'
GROUP BY Village

I have 52 columns I would like to compute into another table to get the averages for each village for each question.
Thanks

2

you’ll need to"unpivot" your columns…

INSERT 
  INTO AverageTest 
     ( Village
     , item
     , calculation )
SELECT Village
     , 'IcanbealonewhenIwish' AS item
     , 100.0 *
       COUNT(CASE WHEN CanbealonewhenIwish IN (3,4)
                  THEN 1
                  ELSE NULL END) /
       COUNT(*)                AS calculation
  FROM Resident_Survey
...

you’ll need one SELECT in the union for each column

3

Duh thank you so much! I did this once in school but haven’t done it since!

4

My Keen intuition tells me you very likely have a bad database design. It would be a good idea to post your DB schema so we could review it for you.

5

They already posted it over here

6

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