Hello, Is it possible to have multi

[zarkov]

Hello, Is it possible to have multiple Count statements in a single query.
this is the code im trying to use.

$DB->query( "SELECT p.*, m.id,m.name,m.mgroup,m.email,m.joined,m.avatar,m.avatar_size,m.posts,m.aim_name,m.icq_number,
m.signature, m.website,m.yahoo,m.integ_msg,m.title,m.hide_email,m.msnname, m.warn_level, m.warn_lastwarn,
g.g_id, g.g_title, g.g_icon, g.g_dohtml, COUNT(t.starter_id) AS tcount, COUNT(e.recipient_id) as pmcount $join_get_fields
FROM ibf_posts p
LEFT JOIN ibf_members m ON (p.author_id=m.id)
LEFT JOIN ibf_groups g ON (g.g_id=m.mgroup)
LEFT JOIN ibf_topics e ON (t.starter_id=m.id)
LEFT JOIN ibf_messages e ON (e.recipient_id=m.id)
$join_profile_query
WHERE p.topic_id=".$this->topic['tid']." and p.queued != 1
GROUP BY p.{$ibforums->vars['post_order_column']} {$ibforums->vars['post_order_sort']} LIMIT $first, ".$ibforums->vars['display_max_posts']);


If i take out either of the 2 counts then all is well but with both in there, both counts have the same value and it is not the value from either one of them.

If this is not possible how would i create a new statement that would append the result to the first query.

If this dont make any sense ill give you an idea of what im trying to do.

this code is from a PHP forum software.
In the original code neither count statements are there.
I want to count the numebr of topics created by and the number of private messages for each member in the posts.

Each works on their own as i said.
I have tried creating a new query but the data does not display. I assume this is due to storing the data in an array somewhere else and my second query is not being added.

Any help on this would be greatly appreciated.
Thanks

 

[r937]

> "I want to count the numebr of topics created by
> and the number of private messages
> for each member in the posts

then you must group by each member of the post, such that the only columns in the GROUP BY are columns with a one-to-one relationship with the member primary key

these columns will then be the ones in yout SELECT list, along with the COUNTs

then you must realize that COUNT(columname) merely counts the number of non-null values in the group, and as it concerns the relationship between topics and messages, there probably will be the same number of non-null values in each group

you are probably going to need DISTINCT inside one of the counts, probably topics

and if topics and messages aren't directly related, you have even more problems, you should probably not be trying to get both counts in the same query

rudy