SQL Help please!!

[travisbrown]

I have been pulling my hair out all day writing and rewriting statements, trying to pull the right info from this table. I'm hoping someone here can help.

I have a flat table that stores responses to a survey. The survey is seven questions with two responses to each question (sts and wts). It has the columns

questionID (identifies the records in sts and wts)
sts (stores responses to sts)
wts (stores responses to wts)
authuser (the user who completed the form)

The data looks like this
<pre>
q | sts | wts | authuser
________________________
1 | weekly | NULL | tbrown
2 | weekly | NULL | tbrown
3 | -7 | NULL | tbrown
4 | hourly | NULL | tbrown
5 | hourly | NULL | tbrown
6 | morn | NULL | tbrown
7 | hourly | NULL | tbrown
1 | NULL | weekly | tbrown23
2 | NULL | hour | tbrown23
3 | NULL | morn | tbrown23
4 | NULL | daily | tbrown23
5 | NULL | -7 | tbrown23
6 | NULL | -8 | tbrown23
7 | NULL | hourly | tbrown23
1 | -7 | weekly | tbrown46
2 | week | hour | tbrown46
3 | week | morn | tbrown46
4 | time | daily | tbrown46
5 | daily | -7 | tbrown46
6 | NULL | -8 | tbrown46
7 | NULL | hourly | tbrown46
</pre>

I need to count the number of users who responded just to sts and just to wts and those who responded to both. The results I am looking for in the example is
sts only= 1 (i.e. user tbrown)
wts only= 1 (i.e. user tbrown23)
both = 1 (i.e. user trown46)

To get the number of sts-only respondents, I tried writing
Select COUNT(DISTINCT authuser) AS Authuser
FROM mytable
WHERE wts IS NULL
but this returns any user that has wts = NULL for any of the seven responses

what I need is to return users where all seven of the wts responses are null and the sts responses are null or have value, and another query that satisfies the opposite ( sts = NULL and wts = value or NULL)

I then tried:
SELECT COUNT(authuser) AS authuser
FROM mytable
WHERE (questionID = 1 AND wts IS NULL) AND (questionID = 2 AND wts IS NULL) AND (questionID = 3... and so on for all seven questions, by the SQL Server 2000 Enterprise manager breaks the WHERE clauses down into (questionID = 1) AND (wts is NULL) and so on.The result set is 0 of course, because no line satisfies the questionID of 1,2,3,4,5,6, and 7

I'm getting a bit frantic. Can anyone help? Thanks

 

[rac2]

Maybe summarize this way -

Select authuser, COUNT(sts), COUNT(wts)
FROM mytable
GROUP BY authuser
HAVING COUNT(sts) > 0 AND COUNT(wts) = 0

 

[tlbroadbent]

Here is one SQL solution.

Select Type, Count(authuser) As Cnt
From (
Select 'STSOnly' As Type, authuser
From tblName
Where wts is null
Group By authuser
Having count(*) = 7

Union All

Select 'WTSOnly' As Type, authuser
From tblName
Where sts is null
Group By authuser
Having count(*) = 7

Union All

Select 'Both' As Type, authuser
From tblName
Where authuser In (Select authuser From tblName Where sts is not null)
And authuser In (Select authuser From tblName Where wts is not null)
Group By authuser) as qry
Group By Type Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[travisbrown]

Thank-you both! I can use both solutions, and they work perfectly!

 

[svanels]

I would suggest that you look back in the forum counting occurences in a table thread220-192148, where I had a similar problem with excellent resplies.

Regards S. van Els
SAvanEls@cq-link.sr