Count on two tables

[stevequal]

Hello,

 

I'd really appreciate any help on this one.

 

I am creating a website that uses a voting a system. I have a table for posts (tblPosts) and a table where votes on the posts are stored (tblPostVotes). I also have a table where people can make Comments on the posts (tblComments). Finally and less importantly I have a join between the posts and members (tblMembers).

 

I want to do the AVG, SUM AND COUNT on the tblPostsVotes joining the related post and member details. I have achieved this with the following query:

 

SELECT ROUND(AVG(tblPostVotes.postScore), 1) AS postAvgScore, SUM(tblPostVotes.postScore) AS postTotalVotes, COUNT(tblPostVotes.postVoteId) AS postVotes, tblPosts.postId, tblPosts.postTitle, tblPosts.postBody, tblPosts.postDateCreated, tblMembers.memberId, tblMembers.memberUsername

FROM (tblPosts INNER JOIN tblPostVotes ON tblPosts.postId = tblPostVotes.postId) INNER JOIN tblMembers ON tblPosts.memberId = tblMembers.memberId

GROUP BY tblPosts.postTitle, tblMembers.memberUsername, tblPostVotes.postId

ORDER BY tblPosts.postDateCreated DESC

LIMIT 0, 10;

 

My problem is I also want to include the total (COUNT) number of comments made for each post returned and I don't know how to do this in one sql statement???

 

My hosting is: PHP Version 5.2.3

 

MySQL Version: 4.1.11

 

Here are my tables:

 

 

CREATE TABLE `tblPosts` (

  `postId` int(10) NOT NULL auto_increment,

  `postTitle` varchar(100) NOT NULL default '',

  `postBody` text,

  `postDateCreated` datetime default NULL,

  `memberId` int(10) NOT NULL default '0',

  PRIMARY KEY  (`postId`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 41984 kB';

 

 

CREATE TABLE `tblPostVotes` (

  `postVoteId` int(10) NOT NULL auto_increment,

  `postId` int(10) NOT NULL default '0',

  `memberId` int(10) NOT NULL default '0',

  `postScore` int(1) NOT NULL default '0',

  `postVoteDateCreated` datetime NOT NULL default '0000-00-00 00:00:00',

  PRIMARY KEY  (`postVoteId`,`postScore`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 PACK_KEYS=0 COMMENT='InnoDB free: 40960 kB';

 

 

CREATE TABLE `tblComments` (

  `commentId` int(10) NOT NULL auto_increment,

  `postId` int(10) NOT NULL default '0',

  `commentBody` text NOT NULL,

  `commentDateCreated` datetime NOT NULL default '0000-00-00 00:00:00',

  `memberId` int(10) NOT NULL default '0',

  PRIMARY KEY  (`commentId`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 40960 kB';

 

 

CREATE TABLE `tblMembers` (

  `memberId` int(10) unsigned NOT NULL auto_increment,

  `memberUsername` varchar(20) NOT NULL default '',

  `memberPassword` varchar(20) NOT NULL default '',

  PRIMARY KEY  (`memberId`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 41984 kB';

 

Any more info needed please let me know.

 

Many thanks

 

 

 

 

 

 

[NArc0t1c]

Why do you want to count the total to the average?

That just seems weird.

For statistic's logging you would get total(then display), average(display), single's(display).

Three separate queries.

 

Please explain for what you are using it for.