Hi I want to get the count of column called 'response'. But even though a user submits many responses I want it to be only considered as one. So basically I want to get the count of 'response' with DISTINCT user_id. How can I do this? Thank you.
+1
A:
I'm not 100% sure I understand your table structure, but maybe you want this?
SELECT COUNT(DISTINCT userid)
FROM Table1
WHERE response IS NOT NULL
Result:
2
Test data:
CREATE TABLE table1 (userid INT NOT NULL, response NVARCHAR(100) NULL);
INSERT INTO table1 (userid, response) VALUES
(1, NULL),
(1, 'a'),
(1, 'b'),
(2, NULL),
(3, 'c');
Note: if the response column cannot be NULL, you don't need the WHERE response IS NOT NULL clause.
Mark Byers
2010-04-24 16:47:39
Actually the answer I'm looking for is 1. i.e even though user has responded 3 times, I'm taking it as 1 in my system. Also what I need is not the count of userid, the count of responses. Hope you understood. Thanks for the quick response.
Thomas Puthoor
2010-04-24 16:52:12
@Thomas Puthoor: In my test data there are three different users and three responses, two of which come from the same user. So as I understand your requirements the answer should be 2, not 1.
Mark Byers
2010-04-24 16:53:31
Yes sorry my mistake. Thanks for your response. Sorry for taking your time.
Thomas Puthoor
2010-04-24 17:20:50
A:
Probably this is what you are looking for,
select user_id ,count(response) from Table1 group by user_id
Srinivas Reddy Thatiparthy
2010-04-24 16:55:56
actually i only want the count of response. with this query i get the answers grouped by user_id. So if there were 3 response by user_a and 1 response by user_b. The answer that I will be getting is 3 and 1. But the actual answer I want is 2 since two users responded.
Thomas Puthoor
2010-04-24 17:09:03