get count of all users for particular category and rank them

Question

how do I get count of each user contribution/appearing for that particular category.The table below has user,category .I am looking for count of hoe many times all users have contributed/appeared in the table below and rank them.

http://sqlfiddle.com/#!2/d4458/2

CREATE TABLE if not exists tblA
(
id int(11) NOT NULL auto_increment ,
user varchar(255),
 category int(255),
 PRIMARY KEY (id)
);


INSERT INTO tblA (user, category ) VALUES
('1', '1'),
('1', '2'),
('1', '3'),
('1', '1'),
('2', '1'),
('2', '1');

Response like: Search for category where its '1'

user  category    count    rank

1       1         2        1
2       1         2        2

score 2 · Accepted Answer · answered Feb 19 '14 at 13:24

2

SELECT USER,
       category,
       count(*) AS num
FROM tblA
WHERE category=1
GROUP BY USER,
         category
ORDER BY num DESC;

demo: http://sqlfiddle.com/#!2/d4458/10/0

answered Feb 19 '14 at 13:24

DhruvPathak

42,059
16
116
175

What is the criteria to rank ? I see both users with same count in categories. – DhruvPathak Feb 19 '14 at 13:28
how can I add another column with rank and have 1 and 2 and so on based on the count ? – jason Feb 19 '14 at 13:28
for rank see this question: http://stackoverflow.com/questions/3333665/mysql-rank-function – bw_üezi Feb 19 '14 at 13:31
http://sqlfiddle.com/#!2/a9188/1 rank them 1,2,3 also if the case where both count is same then also the user who is above will have lower rank.Eg. user 1 and 2 both have 2 so user 1 will be rank 1 and user 2 will be rank 2. – jason Feb 19 '14 at 13:32
@DhruvPathak Can you please tell me how do I do this ? – jason Feb 19 '14 at 13:35
@jason if the answer worked for you upvote it and also accept it :Dhruy your answer is relevant – Humphrey Feb 19 '14 at 13:42
@humphrey I am unable to rank them as in the question I had asked I would also like to rank them currently I am getting count but how do I get another column next to count where it has them ranked based on num – jason Feb 19 '14 at 13:44

score 1 · Answer 2 · answered Feb 19 '14 at 13:51

SET @prev_value = NULL;
SET @rank_count = 0;
SELECT
  i.*,
  CASE 
    WHEN @prev_value = i.num THEN @rank_count
    ELSE @rank_count := @rank_count + 1
  END AS rank
FROM (
  SELECT 
    user,category,COUNT(*) AS num
  FROM tblA
  WHERE category=1 
  GROUP BY user,category 
  ORDER BY num DESC
) i;

get count of all users for particular category and rank them

2 Answers2