MySQL Server Forum - Multiple Count()s?

Author

Multiple Count()s?

Alan Little

2005-08-22, 9:23 am

I have a table with links, a log table to record clicks, and a vote table
to record votes on the links.

Is it possible to get the link data, the click count, vote count and vote
total in one query?

I can get the click count OK, but as soon as I add the vote count to the
query, it multiplies them; e.g. for a link with 14 clicks and 2 votes, it
tells me 28 for both figures. Here's the query:

select lnk.LinkID, URL, LinkTitle, Description, count(log.LinkID) Clicks,
count(Rated) Votes
from fn_links lnk
left join fn_log log using (LinkID)
left join fn_rate vot using (LinkID)
where CatID = $CatID
and Status = $Active
group by lnk.LinkID
order by Created

Then of course I want to include sum(Rating). I tried grouping on
different things, but it makes no difference.

--
Alan Little
Phorm PHP Form Processor
http://www.phorm.com/

Alan Little

2005-08-23, 9:23 am

Carved in mystic runes upon the very living rock, the last words of Alan
Little of mailing.database.mysql make plain:

> I have a table with links, a log table to record clicks, and a vote
> table to record votes on the links.
>
> Is it possible to get the link data, the click count, vote count and
> vote total in one query?

I guess that's a "No"?

--
Alan Little
Phorm PHP Form Processor
http://www.phorm.com/

Sponsored Links