Selecting distinct groups of lines

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Selecting distinct groups of lines

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-30 : 08:25:59

Travis writes "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


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

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."

Nazim
A custom title

1408 Posts

Posted - 2002-01-30 : 08:42:28

One easy way could be this.

select StsResponse=(select count(*) from mytable where sts is not null and wts is null
group by authuser),WtsResponse==(select count(*) from mytable where sts is null and wts is not null
group by authuser),Both=(select count(*) from mytable where sts is not null and wts is not null
group by authuser)

i will also suggest you to have a look at Case statement in BOL.

--------------------------------------------------------------
Dont Tell God how big your Problem is , Tell the Problem how Big your God is

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources