CODEWITHSUNDEEP
mysql
Code Lover
Code Lover

Reputation: 8348

MYSQL: Count type for user id

Please refer the db table image.

enter image description here

I want to count type with value 'Q' for each user id. I am confused either whatever I have written is correct or not.

"SELECT COUNT(type),userid FROM '$db_prefix'posts WHERE type=Q GROUP BY userid";

So where it will display total number of Q for x user id like below :

userid 1 has 25 Q
userid 2 has 11 Q
....

Same way for A and C but I think if I will get for Q than it will work in the same way for A and C.

Upvotes: 0

Views: 616

Answers (3)

Vinod Vishwanath
Vinod Vishwanath

Reputation: 5891

You can actually get it for each type with a single query. Like this:

SELECT type, userid, COUNT(*) AS `n` FROM posts GROUP BY type, userid;

This query groups those rows that have the same value of BOTH type and userid.

Echo statement:

echo $q['userid'] .' has '. $q['n'] $q['type'] .' question';

Upvotes: 2

goodfriend0
goodfriend0

Reputation: 303

This query is ok:

select count(type), userid
from posts
where type='Q'
group by userid

You forgot the quotes on the '**Q *'

This query does all the letter in one shot.

select userid, type, count(*) as count
from posts
group by userid, type

Upvotes: 3

xlecoustillier
xlecoustillier

Reputation: 16351

"SELECT userid, type, COUNT(*) 
FROM {$db_prefix}posts 
GROUP BY userid, type"

Upvotes: 1

Related Questions