Hi.
On Tue, Nov 20, 2001 at 10:40:25AM -0500, [EMAIL PROTECTED] wrote:
>
> I will try to be as complete as possible here. This is the query that I'm
> trying to run:
>
> SELECT
[...]
> art_id,
> art_title,
> art_description,
> typ_id,
> SUM(arp_visits) AS visits,
> COUNT(fed_id) AS comments
> FROM
> users,
> articles,
> article_pages,
> types
> LEFT JOIN
> feedback
> ON
> fed_typ_target_id=art_id AND
> fed_typ_id=typ_id
> WHERE
> usr_id=art_usr_id AND
> usr_status='active' AND
> art_status='approved' AND
> art_id=arp_art_id AND
> typ_name='articles'
> GROUP BY
> art_id
> ORDER BY
> art_created_date DESC
> LIMIT
> 0, 10
>
> Anyway, it all comes down to the following: I'm trying to select the total
> number of comments and visits for a specific 'article', as symbolized as
> art_id. I currently only have 1 article in the 'articles' table, and 3
> comments related to it on the 'feedback' table. This 'article' have 49
> visits currently.
>
> The problem is that the number of visits that I receive from this query is
> actually the result of the multiplication of (comments * visits), which is
> 147.
Well, that's actually what this SQL query asks for. It's easy to see:
Just drop the GROUP BY and replace SUM and COUNT by the resp. columns
and have a look at the output. You should get 3 rows, one for each
comment, paired with all columns of the article.
That's why COUNT(fed_id) is 3, like you want it. The SUM over the 3
rows, with all of them having arp_visit 49, will be 147.
The perceived problem stems from the fact that you try to mix a
grouping fuction intended for "article" with a grouping function for
"feedback". You are actually grouping in a way correct for getting
count of feedback, so grouping an article datum will fail.
In your case, the solution is to simply take in count that you will
SUM the visits multiple times:
SUM(arp_visits)/COUNT(arp_visits) AS visits
[...]
> Cheers,
> Joao
Bye,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
