In a database we have about 1000 registered users. I'd like to know how many of those users have actually written a question or posted an answer. All the information can be take from the tblQA table and the userid is "intPosterID", the questions & answers each have their own ID, "PostID". Is there a query that can be run to count how many unique users have posted a question or answer?
+2
A:
Counting the distinct userIDs can be done via:
SELECT COUNT( DISTINCT intPosterID ) FROM tblQA;
COUNT( DISTINCT field ) returns a count of the number of rows with different values for the given field - intPosterID in this case.
ConroyP
2010-08-23 22:03:45
Getting Resource ID #11 error. $unique = mysql_query("SELECT COUNT( DISTINCT intPosterID ) FROM tblQA)";
BigMike
2010-08-23 22:16:38
Does running the query give you the error, or are you trying to echo $unique? If the latter, it won't work as it's a Mysql resource, not a result yet. Try $res = mysql_query('QUERY_HERE'); $unique = mysql_fetch_row($res); print_r($unique);
ConroyP
2010-08-23 23:05:13
A:
This should do it.
select count(intPosterID)
from tblQA
group by intPosterID;
haydenmuhl
2010-08-23 22:04:52
this will return the number of non-null values in the column, not necessarily the number of different values.
Peter Tillemans
2010-08-23 22:09:11
A:
Count posts per user :
SELECT COUNT(PostID), intPosterID FROM tblQA GROUP BY intPosterId
numbers of results = number of users or run ConroyP query
Benoit
2010-08-23 22:05:12
+1
A:
COUNT(DISTINCT columnname) can be used for that :
SELECT COUNT(DISTINCT intPosterId) FROM tblQA;
Peter Tillemans
2010-08-23 22:07:32