mysql count not working properly?

Question

I am trying to count the total comments posted by single user. Here is the table structure of the comments table:

CREATE TABLE `PLD_COMMENT` (
   `ID` int(11) NOT NULL auto_increment,        
   `ITEM_ID` varchar(11) NOT NULL,
   `USER_ID` varchar(11) NOT NULL,
   `USER_NAME` varchar(255) NOT NULL, 
   `COMMENT` longtext,
   `COMMENT_TITLE` varchar(255) default NULL,
   `COMMENT_RATING` tinyint(1) default '1', 
   `TYPE` int(11) NOT NULL, 
   `DATE_ADDED` timestamp NOT NULL 
       default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
   `IPADDRESS` varchar(15) default NULL,
   `STATUS` varchar(11) NOT NULL, 
    PRIMARY KEY  (`ID`)  
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

Here is the table structure for user table

CREATE TABLE `pld_user`(
    `ID` int(11) NOT NULL auto_increment,
    `LOGIN` varchar(100) NOT NULL,
    `NAME` varchar(255) NOT NULL,
    `PASSWORD` varchar(46) NOT NULL,
    `LEVEL` tinyint(4) NOT NULL default '0',
    `RANK` tinyint(4) NOT NULL default '0',
    `ACTIVE` tinyint(4) NOT NULL default '0',
    `LAST_LOGIN` timestamp NOT NULL 
        default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
    `REGISTRATION_DATE` timestamp NOT NULL default '0000-00-00 00:00:00',
    `AUTH_IMG` varchar(255) default NULL,
    `AUTH_IMGTN` varchar(255) default NULL,
    `SUBMIT_NOTIF` tinyint(4) NOT NULL default '1', 
    `PAYMENT_NOTIF` tinyint(4) NOT NULL default '1', 
    `ADDRESS` varchar(255) default NULL, 
    `EMAIL` varchar(255) NOT NULL,
    `WEBSITE` varchar(255) default NULL, 
    `WEBSITE_NAME` varchar(255) default NULL,
    `INFO` varchar(255) default NULL, 
    `ANONYMOUS` tinyint(4) NOT NULL default '0', 
    `LANGUAGE` varchar(2) default NULL, 
    `AVATAR` varchar(100) default NULL,
    `ICQ` varchar(15) default NULL, 
    `AIM` varchar(255) default NULL, 
    `YIM` varchar(255) default NULL, 
    `MSN` varchar(255) default NULL, 
    `CONFIRM` varchar(10) default NULL,
    `NEW_PASSWORD` varchar(46) default NULL,
    `EMAIL_CONFIRMED` int(11) NOT NULL default '1', 
    `LNAME` varchar(255) default NULL,
    `CITY` varchar(255) default NULL, 
    `STATE` varchar(255) default NULL,
    `DOB` date default NULL,
    `UTYPE` tinyint(1) NOT NULL default '0',
    PRIMARY KEY  (`ID`) 
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 
 COMMENT='Stores all the users with informations'

Here is my query:

SELECT count(c.USER_ID) as total_commments_user , 
  c.*, u.NAME, l.TITLE as LINK_TITLE, u.AUTH_IMG
FROM `PLD_COMMENT` c
left outer join `PLD_USER` u ON (u.ID = c.USER_ID) 
left outer join `PLD_LINK` l ON (l.ID = c.ITEM_ID AND l.STATUS='2') 
WHERE c.TYPE = '1' 
  AND c.STATUS = '2' 
group by c.ID ORDER BY c.ID DESC LIMIT 0 , 3

When I run this query I got 1 in each row under total_comments_user.

Any idea?

Mahmoud Gamal · Accepted Answer · 2012-02-03 08:47:47Z

2

You need to add all the columns you are selecting in the SELECT clause except the c.USER_ID to the GROUP BY clause, like this:

group by c.ID, c.otherfields, l.title,..

EDIT: I think the following will work properly:

SELECT count(c.USER_ID) as total_commments_user , 
       c.*, u.NAME, l.TITLE as LINK_TITLE, u.AUTH_IMG
FROM `PLD_COMMENT` c 
left outer join `PLD_USER` u ON (u.ID = c.USER_ID) 
left outer join `PLD_LINK` l ON (l.ID = c.ITEM_ID) 
group by c.ITEM_ID, c.USER_ID
ORDER BY c.USER_ID, l.ID

Example: If you have the following sample data:

PLD_LINK:

ID   STATUS   TITLE    
1      1      title1
2      2      title2

PLD_USER:
```
ID     NAME
8    Mahmoud
9     Ahmed
```

PLD_COMMENT:

ID   ITEM_ID USER_ID   STATUS
4      1        8        1
5      1        8        1
6      1        8        1
7      2        8        2
8      2        8        2
9      1        9        1
10     1        9        1

Case 1: the user `Mahmoud` is displayed twice:

Then, the previous query will give you the count of the comments for each User and for each item too, like this:

total_commments_user  ID  ITEM_ID   USER_ID   Name
         3            4      1         8     Mahmoud
         2            7      2         8     Mahmoud
         2            9      1         9      Ahmed

Notice that the user Mahmoud is displayed twice with a different count, becouse he has different Item_Id.

Case 2: the user `Mahmoud` is diplayed only one time:

If you want to get the count of comments for each user for all items then you will need to group by only the USER_ID and you will got:

total_commments_user  ID  ITEM_ID   USER_ID   Name  
         5            4      1         8     Mahmoud
         3            9      1         9      Ahmed

As you can see now the user Mahmoud is displayed only one time, becouse we ingonred Item_Id.

You can then filter by status or what ever.

edited Feb 3, 2012 at 8:47

answered Feb 1, 2012 at 15:16

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Hanan Ali Over a year ago

why should i need to do this? if i have 20 fields except the c.USER_ID i have to put them all in group by?

Hanan Ali Over a year ago

i did what you asked me to do ... here is the query and it is still returning the same thing: [code] SELECT count(c.USER_ID) as total_commments_user , u.NAME, l.TITLE as LINK_TITLE, u.AUTH_IMG FROM PLD_COMMENT c left outer join PLD_USER u ON (u.ID = c.USER_ID) left outer join PLD_LINK l ON (l.ID = c.ITEM_ID AND l.STATUS='2') WHERE c.TYPE = '1' AND c.STATUS = '2' group by u.NAME, LINK_TITLE, u.AUTH_IMG ORDER BY c.ID DESC LIMIT 0 , 3 [/code]

Mahmoud Gamal Over a year ago

@HananAli, unfortunately, becouse all fields that you are selecting in your SELECT statements, that are not enclosed in an aggregate function like COUNT or SUM, have to be added in the GROUP BY clause too. This is becouse Count is returns a single value, but we have a lot of values for each fields in the select statements.

Hanan Ali Over a year ago

can you please help me what should be the best way to achieve this? I shall be very thankful to you for this regard

Mahmoud Gamal Over a year ago

@HananAli, Sorry I didn't see the JOIN clasue, but i can't understand your tables' structures and the joins between them but try to Group by the Id of the joined table or the field that the join is based on it and the counted id something like Groub by c.USER_ID, u.ID this should work, BTW: your tables need to be normalized, this is a bad design and defficult to work with

|

Brian · Accepted Answer · 2012-02-01 16:48:12Z

1

That is because the group by clause will group the count by all of the select criteria. So due to different l.title values you will get only a count of user id by l.title.

Basically you make one of your columns your select count. i.e

(select count(*) from tables where) as totalcount

The return is going to give you the count for every instance of that user id and it will show that count in every row for that userid. you will have to add the conditional clauses to make the subselect only look at the rows relevant to that userid.

edited Feb 1, 2012 at 16:48

answered Feb 1, 2012 at 15:16

Brian

2,25918 silver badges24 bronze badges

1 Comment

Hanan Ali Over a year ago

so what should i do in order to get all comments by a user? Do i use sub-query instead of this? Please guide

Collectives™ on Stack Overflow

mysql count not working properly?

2 Answers 2

Case 1: the user `Mahmoud` is displayed twice:

Case 2: the user `Mahmoud` is diplayed only one time:

8 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Case 1: the user Mahmoud is displayed twice:

Case 2: the user Mahmoud is diplayed only one time:

8 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Case 1: the user `Mahmoud` is displayed twice:

Case 2: the user `Mahmoud` is diplayed only one time: