Multiple Count()s?

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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 /

Re: Multiple Count()s?

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

Quoted text here. Click to load it

I guess that's a "No"?

Alan Little
Phorm PHP Form Processor /

Site Timeline