Storing the number of responses -- 1

[link9]

Hello all --

Let me start by describing my table. In it, there are about 50 or so columns, which correspond to survey questions. One column is called 'ourID', which identifies (although not uniquely) where the survey came from. Many surveys, one location -- which, of course, match back to a demographic table.

What I now have to do is store a table with the same number of columns, but instead of the survey responses, I need the number of valid responses by 'ourID'. A valid response equates to anything that IS NOT NULL.

So if, in the demographic table, I have 800 unique ourID's, then this new table would have 800 rows X 50 columns, each column storing the number of valid responses from that ourID on the matching question.

Example:

raw response:
ourID a b c
1 NULL 8 10
1 8 10 NULL
2 NULL 8 10
2 NULL 10 NULL

resulting table
ourID a b c
1 1 2 1
2 0 2 1

I have not been able to devise a SQL solution to this problem, but rather have been in a VB Loop going through each and every ourID and counting the number of valid responses by column, and then storing it in the table -- one iteration of the loop looks at each column by ourID. You can imagine how long this takes w/ nearly 200k records and about 800 ourID's X 50 columns executing over a network. OUCH!

Can someone suggest to me a better way to do this that wouldn't take quite so long (nearly two hours!). I just know it has to be simpler than this.

Thanks, as always.
Paul Prewett

 

[jhall156]

SELECT ourID, SUM(CASE WHEN a IS NULL THEN 0 ELSE 1 END) AS a,
SUM(CASE WHEN b IS NULL THEN 0 ELSE 1 END) AS b,
SUM(CASE WHEN c IS NULL THEN 0 ELSE 1 END) AS c,
SUM(CASE WHEN d IS NULL THEN 0 ELSE 1 END) AS d
FROM Survey
GROUP BY ourID
ORDER BY ourID

 

[link9]

jhall156, consider yourself profusely thanked. Worked great.


paul